How to execute an external .sql file pass-through query in ms-access

Question

I have a couple of pass-through queries stored in ms-access that were created via the query design wizard. I simply copied and pasted the code from my local .sql file into the sql view editor in access and saved it. Everything runs fine.

However, I would like to run my external .sql file from access. My external .sql file is located in a local folder. This way, I don't have to update my external .sql file and my stored query in ms-access when I change something to either query.

For what it's worth, all my pass-through queries are 'SELECT * FROM '

Is there a way to do this ?

You can use VBA to read the contents of the external .sql file into a String and then use that to update the .SQL property of the QueryDef object for the pass-through query. — Gord Thompson

Parfait Parfait · Accepted Answer · 2019-08-27T15:25:47

As @GordonThompson advises, simply read the contents of you .sql (which is a text file like any other programming script) into the .SQL property of a QueryDef (referencing your saved query).

Sub ReadAndSaveSQL()
    Dim LoadFileStr As String
    Dim qdef As QueryDef  

    ' READ .SQL INTO A STRING VARIABLE
    With CreateObject("Scripting.FileSystemObject")
          LoadFileStr = .OpenTextFile("C:\Path\To\Script.sql", 1).readall
    End With

    ' INITIALIZE QUERYDEF
    Set qdef = CurrentDb.QueryDefs("mySavedPassThroughQuery")

    qdef.SQL = LoadFileStr                       ' REWRITE QUERY'S SQL
    qdf.Close                                    ' CLOSE AND SAVE QUERY
    Set qdef = Nothing                           ' UNINITIALIZE QUERYDEF

    DoCmd.OpenQuery "mySavedPassThroughQuery"    ' OPEN SELECT QUERY TO SCREEN
End Sub

How to execute an external .sql file pass-through query in ms-access

1 Answers