CODEWITHSUNDEEP
sqlvbams-access
J.Sutter
J.Sutter

Reputation: 7

Can I run a VBA macro to run SQL queries I have saved in a MS Access database?

I spent alot of time to create 24 different separate MS Access queries that I have saved in the MS Access database. They have to be run in a specific order.

I want to run a macro that will just execute them one after another.

I know I can do this using VBA but I have copy all the SQL into VBA one at a time.

is there a way I can just have VBA run the saved queries in the MS Access database without copying them into VBA?

I am very good with SQL. 15+ years doing Oracle work, Code writing, DB Tuning, Data DBA work.

I am new to using SQL and VBA to run against MS Access DB.

Upvotes: 1

Views: 2444

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112762

You can do something like this to run saved queries:

Dim db As DAO.Database, qry As DAO.QueryDef

Set db = CurrentDb

Debug.Print "-- Start --"
For Each qry In db.QueryDefs
    If qry.Name Like pattern Then
        Debug.Print qry.Name,
        qry.Execute
        Debug.Print "(" & qry.RecordsAffected & ")"
    End If
Next
Debug.Print "-- End --"

db.Close()

Name the queries so that the alphabetical order matches the the expected execution order, e.g., 01_deleteCustomers, 02_appendCustomers, 03_etc.

Specify a pattern string or remove the If alltogether if you want to run all the queries.

Note that in the Visual Basic editor under menu Tools > References... I have selected Microsoft DAO 3.6 Object Library. (You might have another version)

Upvotes: 1

Parfait
Parfait

Reputation: 107767

To clear a few terminologies:

  1. MS Access GUI comprises of five main object types: tables, queries, forms, macros, and modules. Technically, there is no such thing as VBA macros in MS Access. This is a terminology from MS Excel where each subroutine is defined as a macro.

    In MS Access there is a physical object known as a macro which is divorced from VBA (though can call VBA code). Coded routines which includes all functions and subroutines in MS Access are encapsulated in the module (object) usually behind forms, reports, or standalone modules.

  2. Do remember each Microsoft Office application maintains its own object library and should not be conflated with one another. VBA itself is a separate component installed by default (see Tools \ References in IDE). Any language (Java, PHP, Python, etc.) that can run COM-interfacing can connect and run each Office app's object library. VBA is simply one type that does this.

  3. MS Access maintains stored queries (as another object) which can be actions queries (UPDATE, INSERT even CREATE TABLE or ALTER TABLE) or recordset queries (SELECT). You do not have to copy all of SQL code inside VBA to run these saved query objects. In fact, stored queries run more efficiently than string queries called in code since the MS Access engine compiles the best execution plan with stored queries.

Having said that, consider a macro calling multiple OpenQuery actions or coded subroutine inside a module calling multiple DoCmd.OpenQuery commands

Macro

OpenQuery    
   QueryName: myfirstActionQuery
        View: Datasheet
   Data Mode: Edit

OpenQuery    
   QueryName: mySecondActionQuery
        View: Datasheet
   Data Mode: Edit

OpenQuery    
   QueryName: myThirdActionQuery
        View: Datasheet
   Data Mode: Edit

...

For action queries you do not have to close the query as OpenQuery runs process without physically opening anything to screen. You may want to incorporate the SetWarnings to False action in order to avoid the message prompt of how much records will be updated or inserted which is a default setting in Access. Because this is a potentially hazardous action, click Show All Actions to see this specific action.

Module

Public Sub RunQueries()    
     DoCmd.SetWarnings False

     DoCmd.OpenQuery "myfirstSavedQuery"
     DoCmd.OpenQuery "mySecondSavedQuery"
     DoCmd.OpenQuery "myThirdSavedQuery"
     ...

     DoCmd.SetWarnings True
End Sub

Similar to macros, you do not have to close action queries and can call the DoCmd.SetWarnings to suppress the message prompts with each update, insert, or make-table action.

The alternative to turning SetWarnings off and on is @LeeMac's solution which runs queries using the DAO's Database.Execute() command. In fact, because DAO is part of the MS Access object library, the above method can be run outside VBA as mentioned above.

Python

import os
import win32com.client

access_file = r'C:\Path\To\MyDatabase.accdb'

try:
    oApp = win32com.client.Dispatch("Access.Application")
    oApp.OpenCurrentDatabase(access_file)

    db = oApp.Currentdb()
    db.Execute("myFirstSavedQuery")
    db.Execute("mySecondSavedQuery")
    db.Execute("myThirdSavedQuery")
    ...   

except Exception as e:
    print(e)

finally:
    oApp.Quit()
    db = None; oApp = None
    del db; del oApp

Upvotes: 4

Lee Mac
Lee Mac

Reputation: 16025

In its very simplest form, you can define a Sub such as: 

Sub ExecuteQueries()
    With CurrentDb
        .Execute "MyQuery1"
        .Execute "MyQuery2"
        '...
        .Execute "MyQueryN"
    End With
End Sub

Or define this as a Function in a public module if you intend to invoke this from an MS Access Macro.

Upvotes: 3

Fink
Fink

Reputation: 3436

Here is another method if you don't have specific naming conventions utilizing an array for the query names in execution order:

Public Sub RunMasterUpdate()

    Dim qryList As Variant
    Dim i As Long

    qryList = Array("QueryName1", "QueryName2", "QueryName3")

    For i = LBound(qryList) To UBound(qryList)
        CurrentDb.Execute qryList(i)
    Next

End Sub

Upvotes: 1

Related Questions