Runing a query in code (1 Viewer)

[H Pepper]

I want to be able to create some tables in code. I have queries defined to make the tables. Rather than specifying each query to run, I would like to be able to create a loop to execute the queries with names that start "qryMAKE". In this way, if more tables are added later on, I do not have to 'hard code' the names into the vb code. My question is - I'd like to use Execute but this doesn't seem to work as below and I can't see why not.

Dim db As Database, qdf As QueryDef
Set db = CurrentDb

For Each qdf In db.QueryDefs
If Left(qdf.Name,7) = "qryMAKE" Then
db.Execute qdf.Name
Else
Exit Sub
End If
Next
db.close

 

[KenHigg]

From a bigger perspective, it's not generally a good idea to be creating tables in this manner...

Can you shed a little light on why you need the additional tables?

 

[Bat17]

Take the 'exit sub' out of the loop! first time the code finds a query that does not match the pattern it stops running

Peter

 

[H Pepper]

Thanks - taking out the Exit sub certainly did the trick! Can't see the wood for the trees!

The reason I am making tables is to refresh lookup tables that I am using. The lookup tables are updated in an Oracle database and to use these tables for combo boxes for forms is very slow. When user opens database, this routine will run to copy the tables so that they can be used locally. This needs to be done every time the database opens in case the Oracle tables have been updated.

Thanks for your speedy help with this.

 

[KenHigg]

Why can't you simply use delete and append queries to populate the new values and maintain a single table?

 

[H Pepper]

Hello Ken

Yes, I could do this but I thought it might take longer. Now that I have things working, it seems to drop the tables and recreate them very quickly which is good. However, I am not sure what effect this has on memory, bloating of the database etc - do you? Obviously, I'm after the most efficient way of handling all this without crashing the database. Any advice on this would be extremely welcome.

 

[KenHigg]

Hum... The only thing I can come up with is that I would think it 'un-healthy' to allow users to manipulate objects...