0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Roxy
  • 1

4 Answers4

1

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.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
0

I suggest you use a little code and Execute. To quote ms-access - how to automatically select yes in warning message boxes.

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

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Error trapping was not the question. The warning messages was the issue here, correct? – Adriaan Stander Dec 10 '09 at 19:25
  • Without any warnings, queries can fail silently, so it is as well to note the errors. – Fionnuala Dec 10 '09 at 19:40
  • You should never turn of SetWarnings. For a link to the code for my SQLRun function that wraps up a SQL Execute with error handling (and other features) see http://stackoverflow.com/questions/1872554/ms-access-how-to-automatically-select-yes-in-warning-message-boxes/1877234#1877234 . – David-W-Fenton Dec 10 '09 at 20:43
  • @David W Fenton perhaps you mean this comment for a different post? There is no mention of set warnings in this post, furthermore, the link is to the thread which mentions your function. – Fionnuala Dec 10 '09 at 21:10
0

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
Ben McCormack
  • 32,086
  • 48
  • 148
  • 223
  • Using .execute means that docmd.setwarnings isn't required. I also strongly discourage use of docmd.setwarnings in VBA code due to it's masking of additional errors. – Tony Toews Dec 10 '09 at 22:08
  • Is that so? Perhaps I just always put it in without knowing that. I was probably in the exact situation as the question's author and thus that's how I learned to use `DoCmd`. Just curious, what kind of errors might be masked by using `DoCmd.SetWarnings False`? – Ben McCormack Dec 11 '09 at 00:13
  • Actually it's the docmd.runsql errors that are masked by using documd.runsql. So by not using documd.runsql you don't need documd.setwarnings. – Tony Toews Dec 11 '09 at 01:41
-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.

JohnK813
  • 1,134
  • 7
  • 13
  • Ah, misread the question. I have a similar setup that gives me errors, not warnings, so I must have been thinking of that. – JohnK813 Dec 10 '09 at 20:24
  • I shudder whenever I see "On error resume next" other than with a line handling the errors immediately after the statement. – Tony Toews Dec 11 '09 at 01:39