All MS Access queries to SQL file

Question

I have a MS Access database with over 10 queries which need to be updated weekly. It is a real pain to export each one separately and then upload each one separately to the SQL on my server.

I have tried to connect my Access database directly to my SQL server, but my server does not allow that due to security reasons.

I have found a module which will print the queries in one file but it only prints the titles, which is useful but not exactly what I am looking for.

Here is that module code:

Public Sub IterateQueryDefsCollection()

Dim dbMain As DAO.Database

Dim qdf As DAO.QueryDef
Dim qdfTemp As DAO.QueryDef

Set dbMain = CurrentDb

For Each qdf In dbMain.QueryDefs
Debug.Print qdf.Name 'Prints name of query
Set qdfTemp = dbMain.QueryDefs(qdf.Name)
Debug.Print qdfTemp.SQL 'Prints SQL Syntax of query
Next

End Sub

I don't write these codes and am still fairly new to SQL so pretty much my question is...is there a way to have all of my queries along with their data to be exported to a .sql file.

Thank you in advance.

Answer 1

I'm puzzled as to why you'd want to update ten queries each week. Seems to me these should be parameter driven, etc. Note that the following is not moving the data across. Seems to me some append queries in code should handle that quite nicely.

The following is a module I wrote to attempt to upsize all the queries in an Access MDB to the server. Note that as some of these queries were "stacked", that is they called other queries, you had to run this subroutine several times until it couldn't upsize any more.

Sub CopyAllQueriesAsViewsDAO()
    Dim strError As String, strQueryName As String, lngQueryID As Long
    Dim Q As QueryDef, blnSuccessfulQ As Boolean
    Dim strSQL As String, strNewSQL As String, strConnect As String
    Dim intCountFailure As Integer, intCountSuccessful As Integer
    Dim intAlreadyAnError As Integer, strAction As String

    Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef

    On Error GoTo tagError
    
    strConnect = "ODBC;DRIVER={sql server};DATABASE=" & _
        strTestDatabaseName & ";SERVER=" & strSQLServerName & ";" & _
        "Trusted_Connection=Yes"
    DoCmd.Hourglass True


    For Each Q In dbsPermanent.QueryDefs
        intAlreadyAnError = 0
        strQueryName = Q.Name
        If Left(strQueryName, 4) = "~sq_" Then
        Else
            strError = ""
            strAction = ""
            lngQueryID = FetchQueryID(strQueryName, blnSuccessfulQ) ' Add the record or locate the ID
            If blnSuccessfulQ = False Then
                strNewSQL = adhReplace(Q.SQL, vbCrLf, " ")
                strNewSQL = Left(strNewSQL, InStr(strNewSQL, ";") - 1)
                strNewSQL = ConvertTrueFalseTo10(strNewSQL)
                
tagRetryAfterCleanup:
                Set myquerydef = dbsPermanent.CreateQueryDef("") 'Q.Name & " DAO Test")
                myquerydef.ReturnsRecords = False
                myquerydef.Connect = strConnect
                myquerydef.SQL = "CREATE VIEW [" & strQueryName & "] AS " & strNewSQL
                myquerydef.Execute
                myquerydef.Close
        
                strSQL = "UPDATE zCreateQueryErrors SET zcqeErrorMsg = 'Successful' " & _
                    "WHERE ID=" & lngQueryID & ";"
                CurrentDb.Execute strSQL, dbFailOnError
                intCountSuccessful = intCountSuccessful + 1
            End If
        End If
tagResumeAfterError:
    Next

    DoCmd.Hourglass False
    
    MsgBox "There were " & intCountSuccessful & " successful." & vbCrLf & _
        intCountFailure & " failures."

    
    Exit Sub
    
tagError:
    '  MsgBox Err.Description
    
    Dim errX As DAO.Error, strFunctionName As String, intPosnFunction As Integer
    Dim strThisError As String

    If Errors.Count > 1 Then
        For Each errX In DAO.Errors
            strThisError = mID(errX.Description, 48)
            If intAlreadyAnError > 5 Then  ' Hit 10 errors so don't attempt to clean up the query
                If errX.Number <> 3146 Then
                    strError = strError & "After fix: " & errX.Number & ": " & strThisError & " "
                End If
            Else
                Select Case errX.Number
                Case 3146 ' Ignore as this is the generic OLE db error
                Case 195 '  'xxx' is not a recognized function name. > Insert dbo. in front of function name
                    intAlreadyAnError = intAlreadyAnError + 1
                    strFunctionName = mID(strThisError, 2, InStr(2, strThisError, "'") - 2)
                    intPosnFunction = InStr(strNewSQL, strFunctionName)
                    strNewSQL = Left(strNewSQL, intPosnFunction - 1) & "dbo." & mID(strNewSQL, intPosnFunction)
                    strAction = strAction & "Inserted dbo for " & strFunctionName & " "
                    Resume tagRetryAfterCleanup
                ' The ORDER BY clause is invalid in views, .... , unless TOP is also specified.
                Case 1033 'TOP 100 PERCENT
                    strNewSQL = Left(strNewSQL, 7) & " TOP 100 PERCENT " & mID(strNewSQL, 8)
                    strAction = strAction & "Inserted TOP 100 PERCENT "
                    Resume tagRetryAfterCleanup
                Case Else
                    strError = strError & errX.Number & ": " & mID(errX.Description, 48) & " "
                End Select
            End If
        Next errX
    Else
        strError = Err.Number & ", " & Err.Description
    End If

    strSQL = "UPDATE zCreateQueryErrors SET zcqeErrorMsg = '" & adhHandleQuotes(strError) & "', " & _
        "zcqeAction = '" & strAction & "', zcqeFinalSQL = '" & adhHandleQuotes(strNewSQL) & "' " & _
        "WHERE ID=" & lngQueryID & ";"
    CurrentDb.Execute strSQL, dbFailOnError
    intCountFailure = intCountFailure + 1
    Resume tagResumeAfterError
    
End Sub

Public Function ConvertTrueFalseTo10(strIncoming As String)

    Dim strIntermediate As String, intPosn As Integer

    strIntermediate = strIncoming

    intPosn = InStr(strIntermediate, "=false")
    While intPosn <> 0
        strIntermediate = Left(strIntermediate, intPosn - 1) & "=0" & mID(strIntermediate, intPosn + 6)
        intPosn = InStr(strIntermediate, "=false")
    Wend

    intPosn = InStr(strIntermediate, "=true")
    While intPosn <> 0
        strIntermediate = Left(strIntermediate, intPosn - 1) & "=1" & mID(strIntermediate, intPosn + 5)
        intPosn = InStr(strIntermediate, "=true")
    Wend


    ConvertTrueFalseTo10 = strIntermediate

End Function


Function FetchQueryID(strQueryName As String, blnSuccessfulQ As Boolean) As Long

    Dim myRS As Recordset
    Dim strSQL As String
    blnSuccessfulQ = False

    strSQL = "SELECT ID, zcqeErrorMsg FROM zCreateQueryErrors " & _
        "WHERE zcqeName='" & strQueryName & "';"
    Set myRS = dbsPermanent.OpenRecordset(strSQL, dbOpenSnapshot)
    If myRS.EOF Then
        Set myRS = dbsPermanent.OpenRecordset("zCreateQueryErrors", dbOpenSnapshot)
        myRS.AddNew
        myRS!zcqeName = strQueryName
        myRS.Update
        myRS.Move 0, myRS.LastModified
        FetchQueryID = myRS!ID
    Else
        myRS.MoveFirst
        FetchQueryID = myRS!ID
        If myRS!zcqeErrorMsg = "Successful" Then
            blnSuccessfulQ = True
        End If
    End If
    myRS.Close
    Set myRS = Nothing

End Function

Public Function adhHandleQuotes(strValue As String) As String
    ' Fix up all instances of a quote within a string by
    ' breaking up the string, and inserting Chr$(34) whereever
    ' you find a quote within the string.  This way, Jet can
    ' handle the string for searching.
    '
    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997.  All rights reserved.
    '
    ' Solution suggested by Jurgen Welz, a diligent reader.

    ' In:
    '   strValue:   Value to fix up.
    ' Out:
    '   Return value: the text, with quotes fixed up.
    ' Requires:
    '   adhReplace (or some other function that will replace
    '       one string with another)
    '
    ' Example:
    '    adhHandleQuotes("John "Big-Boy" O'Neil") returns
    '     "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

    Const QUOTE As String = """"
    Const SingleQUOTE As String = "'"

    adhHandleQuotes = adhReplace(strValue, SingleQUOTE, _
    SingleQUOTE & SingleQUOTE)
End Function

Function adhReplace(ByVal varValue As Variant, _
ByVal strFind As String, ByVal strReplace As String) As Variant

    ' Replace all instances of strFind with strReplace in varValue.

    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997.  All rights reserved.

    ' In:
    '    varValue: value you want to modify
    '    strFind: string to find
    '    strReplace: string to replace strFind with
    '
    ' Out:
    '    Return value: varValue, with all occurrences of strFind
    '     replaced with strReplace.

    Dim intLenFind As Integer
    Dim intLenReplace As Integer
    Dim intPos As Integer

    If IsNull(varValue) Then
        adhReplace = Null
    Else
        intLenFind = Len(strFind)
        intLenReplace = Len(strReplace)

        intPos = 1
        Do
            intPos = InStr(intPos, varValue, strFind)
            If intPos > 0 Then
                varValue = Left(varValue, intPos - 1) & _
                strReplace & mID(varValue, intPos + intLenFind)
                intPos = intPos + intLenReplace
            End If
        Loop Until intPos = 0
    End If
    adhReplace = varValue
End Function

Answer 2

You ask if there's a way to export a SQL file for this. There is, but you have to write the code to do it, i.e., walk through each row of your query results and write an insert statement (i.e., INSERT INTO ( Field1, Field2 ) VALUES ( value1, value2 ) ( value3, value4), etc.) that is in the correct SQL dialect for the target database engine.

However, it might just be easily to export to a CSV file (or tab-delimited or whatever) and have your database import that file.

There's no real way to know how to answer your question, though, as there's not enough detail on what the queries actually do. If they are INSERT statements, the above will be exactly what you want.

If they are UPDATES, it's more complicated.

But perhaps the above can get you started.

Answer 3

Your SQL server's authentication process does not 'know' who or what application is is attempting to connect to it. If you provide valid credentials, then it should accept the connection attempt. What type of credentials do you provide when you connect to the SQL Server normally ? (when it works) Use the same ones when you specify the credentials that Access attempts to connect with, and the Access connection will also work. If you created a linked table(s) inside access, Access should ask you to specify these connection credentials. Once you have a valid linked Table, connected to the sql server, you will be able to run the Access queries against the SQL Server Table (using the Access Linked Table as a pointer)