MS Access - execute a saved query by name in VBA

Question

How do I execute a saved query in MS Access 2007 in VBA?

I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query.

This doesn't work ... VBA can't find the query.

CurrentDb.Execute queryname

Middlebrow

Answer 1

You can do it the following way:

DoCmd.OpenQuery "yourQueryName", acViewNormal, acEdit

OR

CurrentDb.OpenRecordset("yourQueryName")

Macswan

I do not want to edit. Will this execute my update and insert queries? – Middlebrow

Yes it will run Update and Insert queries. – Macswan

Its an append query that works manually, but not in my vba code. Can I not use a append saved query? – Middlebrow

it should work without any issues, can you edit your question and post more code? – Macswan

Answer 2

You should investigate why VBA can't find queryname.

I have a saved query named qryAddLoginfoRow. It inserts a row with the current time into my loginfo table. That query runs successfully when called by name by CurrentDb.Execute.

CurrentDb.Execute "qryAddLoginfoRow"

My guess is that either queryname is a variable holding the name of a query which doesn't exist in the current database's QueryDefs collection, or queryname is the literal name of an existing query but you didn't enclose it in quotes.

Edit: You need to find a way to accept that queryname does not exist in the current db's QueryDefs collection. Add these 2 lines to your VBA code just before the CurrentDb.Execute line.

Debug.Print "queryname = '" & queryname & "'"
Debug.Print CurrentDb.QueryDefs(queryname).Name

The second of those 2 lines will trigger run-time error 3265, "Item not found in this collection." Then go to the Immediate window to verify the name of the query you're asking CurrentDb to Execute.

Chipman

Its an append query that works manually, but not in my vba code. Can I not use a append saved query? – Middlebrow

The query I described is an append query ... it adds (appends) a row to my loginfo table. – Chipman

Answer 3

To use CurrentDb.Execute, your query must be an action query, AND in quotes.

CurrentDb.Execute "queryname"

Achernar

Answer 4

Thre are 2 ways to run Action Query in MS Access VBA:

---

1. You can use DoCmd.OpenQuery statement. This allows you to control these warnings:

BUT! Keep in mind that DoCmd.SetWarnings will remain set even after the function completes. This means that you need to make sure that you leave it in a condition that suits your needs

Function RunActionQuery(QueryName As String)
    On Error GoTo Hell              'Set Error Hanlder
    DoCmd.SetWarnings True          'Turn On Warnings
    DoCmd.OpenQuery QueryName       'Execute Action Query
    DoCmd.SetWarnings False         'Turn On Warnings
    Exit Function
Hell:
    If Err.Number = 2501 Then       'If Query Was Canceled
        MsgBox Err.Description, vbInformation
    Else                            'Everything else
        MsgBox Err.Description, vbCritical
    End If
End Function

---

2. You can use CurrentDb.Execute method. This alows you to keep Action Query failures under control. The SetWarnings flag does not affect it. Query is executed always without warnings.

Function RunActionQuery()
    'To Catch the Query Error use dbFailOnError option
    On Error GoTo Hell
    CurrentDb.Execute "Query1", dbFailOnError
    Exit Function
Hell:
    Debug.Print Err.Description
End Function

It is worth noting that the dbFailOnError option responds only to data processing failures. If the Query contains an error (such as a typo), then a runtime error is generated, even if this option is not specified

---

In addition, you can use DoCmd.Hourglass True and DoCmd.Hourglass False to control the mouse pointer if your Query takes longer

Weisler