Creating an append query using vba and sql

[dominofly]

Hi Guys,

Im need a little help please.
I written some code which really acts as an append query. The reason i chose to do it through code and not a proper append query is that there are about 20 tables each representing a different country and i would only need to use one of those countries, but i only know which one to use after i have done some other analysis and found that i might be missing a record for that country.

At the moment im getting a Run-time error 424: object required and not sure what is missing. this is line that gives me the error

Set qCo = Object.CreateQueryDef(zCo)

This is the entire sub:

Private Sub cmdRunLine_Click()
Dim zCo     As String
Dim qCo     As QueryDef
Dim zLine   As String
Dim zSQL    As String

If IsNull(txtCompany) Then
    MsgBox "You must enter the company name you intend to update!!"
End If
If IsNull(txtNominal) Then
    MsgBox "You need to enter the nominal details before you continue!!"
End If
zCo = "qap" & ("txtCompany") & "SGA1"
zLine = (txtNominal)
Set qCo = Object.CreateQueryDef(zCo)

zSQL = "INSERT INTO TEMP_SGA_CM ( Company, Our_Reference, [Year], Period, Cost_Centre, Reference, Transaction_Date, Description, Job_Code, GL_Code, Department_Code, [Value], Rate, Net_Value, STG, Department, GL_Group, Description_GL, Description_CC )" & _
"SELECT Company, idxOurRef1, thYear, thPeriod, tlCostCentre, thAcCode, tlTransDate, tlDescr, tlJobCode, tlGLCode, tlDepartment, value, xRate, NetValue, Stg, Desc, GL_Group, Description_GL, Description" & _
"FROM qCo" & _
"WHERE qCo.idxOurRef1 = 'zLine'"
 CurrentDb.Execute zSQL, DB_FAILONERROR
End Sub

 

[revans]

The string you are passing the Execute method doesn't result in a proper append query. Try this

Private Sub cmdRunLine_Click()
Dim zCo     As String
Dim zLine   As String
Dim zSQL    As String
If IsNull(me!txtCompany) Then
    MsgBox "You must enter the company name you intend to update!!"
    If IsNull(me!txtNominal) Then
       MsgBox "You need to enter the nominal details before you continue!!"
    else
      zCo = "qap" & replace(me!txtCompany,"'","''") & "SGA1"
      zLine = Replace(me!txtNominal,"'","''")
 
      zSQL = "INSERT INTO TEMP_SGA_CM ( Company, Our_Reference, [Year], Period, Cost_Centre, Reference, Transaction_Date, Description, Job_Code, GL_Code, Department_Code, [Value], Rate, Net_Value, STG, Department, GL_Group, Description_GL, Description_CC ) " & _
"SELECT Company, idxOurRef1, thYear, thPeriod, tlCostCentre, thAcCode, tlTransDate, tlDescr, tlJobCode, tlGLCode, tlDepartment, value, xRate, NetValue, Stg, Desc, GL_Group, Description_GL, Description " & _
"FROM " & qCo & _
" WHERE idxOurRef1 = '" & zLine & "'"
     CurrentDb.Execute zSQL, DB_FAILONERROR
end if
end if
End Sub

hth,

Rich

 

[JimmyMack]

I'm having a similar problem and cannot find my syntax error(s). I'm getting the same 'Run-time error 424, object required' when I try to execute strAppendQuery (in the section labeled 'Append SamplingEvent Data'). I think it likely the error has something to do with incorrect use of quotations but when I MsgBox the string it looks identical to the SQL string generated when I make query through the GUI interface (that runs fine, by the way). I'm running Windows 7 and MS Office 2010. Any help would be greatly appreciated!

Here is my code:

Private Sub cmdImportFldData_Click()


    Dim ImportFldData As String, Records As Integer, strAppendQuery As String, FldDataImport As DAO.Recordset
    
'Set Import Table Name
    EDDPath
    ImportFldData = strFolderName + cboSourceFldData.Value
    
    On Error GoTo ErrHandler
    
'Import EDD as tblNewSVLEDD
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblNewFieldDataImport", ImportFldData, True, "CombinedSampleResults!"
    
    Set FldDataImport = CurrentDb.OpenRecordset("tblNewFieldDataImport", dbOpenDynaset)
    FldDataImport.MoveLast
    Records = FldDataImport.RecordCount
    
    MsgBox "SVL WQ table imported successfully as tblNewFieldDataImport" & vbNewLine & "with " & Records & " records.", vbInformation
    
'Append SamplingEvent Data
    strAppendQuery = "INSERT INTO tbl2_SamplingEvent ( CollectionId, SiteNumber, MatrixName, SampleDate, SampleTime, Collector, " & _
        "DateLastUpdated, DataSource ) SELECT [tblNewFieldDataImport]![SiteNbr] & " & Chr(34) & "_" & Chr(34) & _
        " & Year([tblNewFieldDataImport]![SampleDate]) & Format(Month([tblNewFieldDataImport]![SampleDate])," & _
        Chr(34) & "00" & Chr(34) & ") & Format(Day([tblNewFieldDataImport]![SampleDate])," & Chr(34) & "00" & Chr(34) & _
        ") AS CollectionId, tblNewFieldDataImport.SiteNbr, " & Chr(34) & "Water" & Chr(34) & " AS MatrixName, tblNewFieldDataImport.SampleDate, " & _
        "tblNewFieldDataImport.SampleTime, " & Chr(34) & "B. Johnson" & Chr(34) & " AS Collector, " & _
        "Now() AS DateLastUpdated, tblNewFieldDataImport.DataSource FROM tblNewFieldDataImport;"

    db.Execute strAppendQuery, dbFailOnError


    GoTo ExitImport
        
ErrHandler:
    MsgBox "Error importing SVL WQ table" & vbNewLine & "-------------------------------------------------------------------" & _
    vbNewLine & "ERR#" & Err.Number & ": " & Err.Description, vbExclamation
    GoTo ExitImport


AppendErr:


ExitImport:
End Sub

 

[JimmyMack]

Okay...I've solved my issue. If anyone is experiencing a similar error try executing your SQL string using:

DoCmd.RunSQL stringVariable

instead of

db.Execute stringVariable, dbFailOnError

Apparently, "RunSQL automatically attempts to evaluate and fill in values for parameters in the SQL string -- and references to form controls count as parameters. Execute doesn't do that, so you have to either build the values into the string, or else take extra steps to resolve the parameters." Which I found here: Docmd.RunSQL() VS db.Execute()
Dirk Goldgar also provides an example of how to work around this issue using db.Execute if you are so inclined.