Run queriess iteratively from within a macro or [Solved]

[DennisW]

I want to run a set of queries iteratively from within a macro or Basic program.

I have a set of queries that where originally created in MS Access. I moved my Access database to Base in OO. The queries all function well in Base (they provide valid results so there is no problem there).

I run an initiating query that performs calculations based on data from 3 source data tables and enters (appends) the result into results table1. After several iterations of the initiating query, a second query appends the data from results table1 into results table2. The data in results table1 is then deleted. and the process is re-initiated only this time a 3rd query is used. The 3rd query uses the data from the 3 source data tables plus the results table2 to perform calculations, and the results are then entered (appended) into results table1. As previously specified, after a given number of iterations of this process, the data from results table1 are appended to results table2. The data in results table1 is deleted, and the process is started again using the 3rd query.

The process is repeated (iterated) a specified number of times.

I want to automate the process, so I would like to simply run the existing queries from within a macro or program that would automatically perform the specified number of iterations.

I need to know the syntax for launching (running) queries from within a macro or Basic program that would control the iterative process.

I have read the beginners guide for Base, and I have read the Developer Guide.

In visual basic there is the command "DoCmd.OpenQuery" like this

DoCmd.OpenQuery "queryname", acViewNormal, acEdit
DoCmd.Close

This command allows me to launch, from within visual basic, a query that was created and saved in the Access database.

I have not been able to find or identify a corresponding command in Basic, and I don't know how create macros without a form.


Dennis W
_________________
Dennis W

[RPG]

Hello

Study pdf of Benitez.
http://www.baseprogramming.com/resources.html
The last two lines

I think study also this post.
http://www.oooforum.org/forum/viewtopic.phtml?t=102514&highlight=

I think also look for the programming language tool make by the HSQLDB team. That can maybe more easy then learning macros in OOo.

Romke

[Sliderule]

Dennis:

Welcome to OpenOffice Base.

This may be more information compared to what you asked, but, thought I would post it anyway . . . for you and others reading this.

The code below, gets a Query defined in your "Registered Data Name" . . . and . . . runs it.

For documentation of the command:

1. API commands by name
   
   http://api.openoffice.org/docs/common/ref/index-files/index-1.html
   
   
2. Get Query By Name ( Get the SQL defined for the Query )
   
   http://api.openoffice.org/docs/common/ref/com/sun/star/container/XNameAccess.html#getByName
   
   
3. Execute Query ( Run It )
   
   http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XPreparedStatement.html#executeQuery

SAMPLE MACRO CODE to run a Query stored in the "registered database". Includes some 'error messages'.

[DennisW]

Great response.

I appreciate the help.

Thanks
_________________
Dennis W