New Question: How do I automate queries?

[admessing]

I have a photo archive DB and have managed to combine rows from the original data entry tables (via form) based upon comparison of three fields and return a single row. But, this has to go through 3 different queries, & creating a [Dupe] table for the second query.



How do I automate creating the [Dupe] table & get three queries to run sequentially with a single click? I am open to VB scripting if that is what it takes.....I just need to know EXACTLY how to do it, so detailed instructions and a useable sample code would be great.

I need to have this down to just a single operation since most of the other users of this DB will have little to no experience, will be adding records daily and crunched for time.

[pbaldy]

Try

CurrentDb.Execute "FirstQueryName"
CurrentDb.Execute "SecondQueryName"

http://www.baldyweb.com/FirstVBA.htm

[admessing]

Where do I place the recommended code?

[pbaldy]

Where/when do you want it to run? As noted in the link, it's a matter of choosing the appropriate event.

[admessing]

I want it to run as soon as the form used for data entry is closed. That way it doesn't get missed by the other users. Also, I need to create a duplicate of the table created by the first query (called Dupe) before the second and third queries run.

[pbaldy]

Then either in the close event of the form, or behind your "Close" button if you have one. The former would be more reliable if users might close the form via the "X" instead of your button. Creating and copying tables is usually a sign of a design problem, but you can copy a table with the CopyObject method. More info in Help.

[admessing]

When I try to do the "CopyObject" method, I get an error stating that the tabel already exists. The following is what I wish to do on closing the data entry form:

Run "Query-Make Table 1"
Duplicate "Table 1"
Run "Query 2"
Run "Query-Make table 2"

If I can get this to run, do I need to have my users delete/rename the old tables that resulted from a previous run of he queries, or is there a way to automatically delete the old and replace the new? I have no problems with the queries when run manually as they prompt saying that they will replace the original table(s).

[pbaldy]

If you're sure it exists:

CurrentDb.Execute "DROP TABLE TableName;"

[admessing]

Well.....that dealt with the issue of it telling me a table already existed, now I am getting an error relating to the running of the second query. My VB is as follows (red indicates debug error):

[VB]
Private Sub Form_Close()
CurrentDb.Execute "DROP TABLE Photo_Link;"
CurrentDb.Execute "DROP TABLE Dupe;"
CurrentDb.Execute "Photo_Link_Query"
DoCmd.CopyObject "Dupe, acTable, Photo_Link)"
CurrentDb.Execute "Combine_Records Part 1"
CurrentDb.Execute "Combine_Records Part 2"
End Sub
[/VB]

[pbaldy]

I wonder what the error is?

[admessing]

I have no idea....hence why I am asking. IF you were in my shoes how would you write it?

I have three queries:

*Photo_Link_Query (makes table:Photo_Link)

*Combine_Records Part 1 (uses table: Photo_Link and a duplicate of said table named "Dupe" to compare records)

*Combine Records Part 2 (uses output from CR Part 1 and removes all records with a null value in a certain field and creates a new table)

The second query string is where the issue comes in since it is using a table that may already have been created in a previous run, and a duplicate of that table.

[pbaldy]

There's no error message? If the part 1 query is just a SELECT query (doesn't create a table, append/delete/update records), you can't execute it. If that's the case, try just executing part 2. It will run part 1 itself.