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

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

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 ?

Solution

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