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

User · Accepted Answer

You can do it the following way   DoCmd OpenQuery  yourQueryName   acViewNormal  acEdit   OR  CurrentDb OpenRecordset  yourQueryName

User · Answer

Thre are 2 ways to run Action Query in MS Access VBA    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    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  quot Query1 quot   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

User · Answer

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

User · Answer

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       amp  queryname  amp      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

[sql] MS Access - execute a saved query by name in VBA

Examples related to sql

Examples related to ms-access

Examples related to vba