asked on

MS Access QueryDef.SQL Modification Not Working Properly During DAO Transaction

I am having a problem with some VBA code when it runs within a DAO Transaction. The code works fine when it is NOT running within a DAO transaction. Here are the specifics of the problem:
I modify a QueryDef.SQL property, and then I use that QueryDef in a DoCmd.TransferSpreadsheet acExport command. When that command is run I get the run-time error "3066: Query must have at least one destination field." In debug mode I can see that the SQL for the QueryDef is set to the value that I set it to in the previous step. It's a valid SELECT statement, and again, it normally works just fine when I remove the DAO transaction surrounding the code. Interestingly, when I open the query object in Access it is blank. Another interesting fact is that while the debugger is waiting at the DoCmd.TransferSpreadsheet command I can use the Immediate window to commit the transaction, and then when I resume the execution of the code it will work just fine.

So, it appears that the SQL in a QueryDef cannot be modified during a DAO transaction, but does anyone know why?

Dale Fye

Have never really tried that. Have you considered altering the querydef.SQL property outside of the transaction?

Dale

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Can you post the relevant code (not in the code window - just in the comment box) ?

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ken Love

ASKER

Here is a sanitized version of the code:

Public Sub ExcelExport()
    On Error GoTo ErrHandler
    DAO.DBEngine.SetOption DAO.SetOptionEnum.dbMaxLocksPerFile, 1000000
    
    Dim wks As DAO.Workspace
    Dim db As DAO.Database
    Set wks = Application.DBEngine.Workspaces(0)
    Set db = wks.Databases(0)
    
    ' #### Begin transaction.
    ' If I comment-out the transaction commands the procedure works.
    wks.BeginTrans
    Dim TransactionStarted As Boolean
    TransactionStarted = True
    
    ' Track in the export history table.
    Dim ExportHistoryID As Long
    db.Execute "Insert Into [_ExportHistory] ([ExportTimestamp]) Select '" & Now() & "'", DAO.RecordsetOptionEnum.dbFailOnError
    ExportHistoryID = db.OpenRecordset("Select @@Identity").Fields(0).Value
    
    Dim qdfTemp As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim Field2 As String, FilePath As String, SQL As String
    Set qdfTemp = db.QueryDefs("_qryTempQueryDef")
    Set rst = db.OpenRecordset("qryExportQuery", DAO.RecordsetTypeEnum.dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF
        Field2 = rst.Fields("Field2").Value
        FilePath = "C:\Exports\" & Field2 & ".xlsx"
        SQL = "SELECT [Field1] FROM [qryExportItems] WHERE [Field2] = '" & Field2 & "'"
        ' Set the SQL statement of the query def.
        qdfTemp.SQL = SQL
        ' Export.
        ' During transaction (Error 3066: Query must have at least one destination field.)
        Application.DoCmd.TransferSpreadsheet Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel12Xml, qdfTemp.Name, FilePath, True
        ' Track in child1 export history table.
        db.Execute "Insert Into [_ExportHistory_Child1] ([ExportHistoryID], [ExportTimestamp], [Field2]) Select '" & ExportHistoryID & "', '" & Now() & "', '" & Field2 & "'", DAO.RecordsetOptionEnum.dbFailOnError
        
        ' Move to the next item for export.
        rst.MoveNext
    Loop
        
    ' Write exported fields back to the database.
    db.QueryDefs("qryUpdateAfterExport").Execute DAO.RecordsetOptionEnum.dbFailOnError
    
    ' #### Commit transaction.
    ' If I comment-out the transaction commands the procedure works.
    wks.CommitTrans

ExitSub:
    Set rst = Nothing
    Set qdfTemp = Nothing
    Set db = Nothing
    Set wks = Nothing
    Exit Sub
    
ErrHandler:
    If ((Application.DBEngine.Errors.Count > 0) And (TransactionStarted)) Then
        ' If I comment-out the transaction commands the procedure works.
        wks.Rollback
        GoTo ExitSub
    Else
        Err.Raise VBA.Constants.vbObjectError, "ExcelExport"
    End If
End Sub

Open in new window

PatHartman

Did you try my suggestion to use a parameter in the query instead of updating the SQL inside the transaction?

Here's an example that shows how to pass the parameter.

Private Sub TestExport()

    Set db = CurrentDb()
    Set qd = db.QueryDefs("qExportCHIPAADemographics")
        qd.Parameters![EnterFromDT] = [Forms]![frmCHIPAAExports]![txtFromDT]
        qd.Parameters![EnterThruDT] = [Forms]![frmCHIPAAExports]![txtThruDT]
    Set rs = qd.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Call ExportToCSV(rs, "C:\Pat\testexport.csv", False)
End Sub

Open in new window

Ken Love

ASKER

I originally wanted to use a parameterized query, but I didn't think of your idea to store the variable in a hidden form field. I was originally going to pass in the parameter using VBA, but I believe that will only work with a QueryDef object, which then must be opened or executed. That wasn't an option for me, because the DoCmd.TransferSpreadsheet requires the query name. Now I can avoid passing in the parameter directly by doing as you suggested, and put it n a hidden form control.

PatHartman

You're welcome. I use this method frequently since the OutputTo doesn't support a where clause and neither do the TransferText and TransferSpreadsheet methods.