Running 4 make table queries on a time schedule with no human interaction

Question

I have four make table queries that need to be ran in a certain order and on a daily time schedule. I tried creating a macro that would open them in order but it required human interaction to exit out of the warnings and it failed to move on to the 2nd query. Any ideas?

Answer 1

First thought: have you tried adding

On Error Resume Next

to your macro? Not the cleanest way of doing things, but it will get the job done.

Answer 2

I suggest you use a little code and Execute. To quote http://stackoverflow.com/questions/1872554/ms-access-how-to-automatically-select-yes-in-warning-message-boxes/1873705#1873705.

---

It is generally best to use Execute in such cases in order to trap errors:

Dim db As Database, qdf As QueryDef, strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Query17")
qdf.Execute dbFailOnError
Debug.Print qdf.RecordsAffected

Or

strSQL="UPDATE SomeTable SET SomeField=10"
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

Trapping errors with dbFailOnError and an error trap is more or less essential and there are a number of other useful aspects to the Execute Statement

Answer 3

Don't run make table. Leave the tables there but just clear out the records using a delete query.

Also see the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. This avoids bloating your database file.

Answer 4

I agree with Tony Toews in that you should probably avoid MakeTable. When I first used Access, I would use MakeTable queries to update data in tables. I soon found out that this was a really wonky way of doing things.

I would suggest that you convert your MakeTable queries to append queries and make a separate "delete query" for each of the tables that wipes out each item in your original tables (I've also created a "universal" delete query in the pass with a corresponding method that takes in a table name and then rewrites the SQL in the universal delete method to match that table. However, it's probably easier to just create separate delete queries).

I might then write a procedure to execute the queries progamatically. I'm pretty sure the below code should work (though I usually use DAO and create local variables for db (the currentDb) and qdf (the query def), in this example I just run it all from CurrentDb(), which I believe will work).

Sub UpdateMyTables()
    'Turn Warnings Off              '
    DoCmd.SetWarnings False

    'Wipe Data from Tables        '
    CurrentDb().QueryDefs("qryDelete Table1").Execute
    CurrentDb().QueryDefs("qryDelete Table2").Execute

    'Update the tables with the new data from append       '
    CurrentDb().QueryDefs("qryAppend Table1").Execute
    CurrentDb().QueryDefs("qryAppend Table2").Execute

    'Turn Warnings back On        '
    DoCmd.SetWarnings True
End Sub