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

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)

Answer 2

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 3

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.