Run several make table queries in 1 click (1 Viewer)

[smally]

Hi. My Access DB contains 6 make table queries. I use these to create/overwrite tables to a different access DB. The different DB is stored on a shared network folder which is based at a different office, so every time I run one of these make table queries it has to go through the internet.

Also, every time I run a query I get 3 warning messages.
One asking me if I'm sure I want to run it.
Another telling me the existing table will be deleted, should I continue
And a final one telling me I'm about to post X rows into a new table.

Is there anyway for me to click a button and Access run all 6 of these make table queries and to auto confirm all the above mentioned warning messages.

 

[Minty]

What code have you tried that doesn't work ? What are the names of the queries?

You can use

currentdb.execute yourQueryName, dbfailonerror

to run a query automatically.

 

[smally]

I've yet fully get my head around how VBA and Access work so my efforts are not great.

The best I've done is create a macro that performs 6 OpenQuery commands. But this method doesn't hide the 3 warning messages. Which means I have to click yes 18 times.

I tried your method but I get a debug error stating the table already exists.

 

[Minty]

Sorry - I missed that they were make table queries. You will need to delete them all first.

DoCmd.DeleteObject acTable, "YourTableName"

Will delete a table, but will error out if it doesn't exist. I would suggest you add the following function to check if the target table exists first.

Create a new module and put the follwoing code in. Save the Module as BasicFunctions.

Public Function TableExists(sTable As String) As Boolean
   
    If DCount("*", "msysobjects", "Type = 1 AND name='" & sTable & "'") = 0 Then
        TableExists = False
    Else
        TableExists = True
    End If
End Function

Then in your code to delete the tables first;

If TableExists("YourTableName") Then
        DoCmd.DeleteObject acTable, "YourTableName"
End If

 

[RogerCooper]

You can turn off the messages with the SetWarnings action. You may also want to change your preferences in options\client settings\confirm to eliminate some messages.

 

[smally]

I've used this and it works as expected

Function MakeTables()

    DoCmd.SetWarnings False
    
    DoCmd.OpenQuery "qmakExportTrainingMatrix", acViewNormal, acEdit
    DoCmd.OpenQuery "qmakExportRequirementsMatrix", acViewNormal, acEdit
    DoCmd.OpenQuery "qmakExportLGVMatrix", acViewNormal, acEdit
    DoCmd.OpenQuery "qmakTheoryBookingsSchedule", acViewNormal, acEdit
    DoCmd.OpenQuery "qmakTBT", acViewNormal, acEdit
    
    DoCmd.SetWarnings True

End Function

Is there any way to go a bit further for better efficiency. For example each of these queries make a table in a different Access DB, and my guess is when one of these commands run, the program opens connection > performs query > closes connection > and repeat.

Is there a way to open connection > perform all queries > close connection