Run Append Query Through VBA

[jo15765]

I know you can use a delete query in vba by

DoCmd.RunSQL "DELETE * FROM table1"

Can you do the same with an Append query? Everything I see says to create an actual query and call the queryname from your vba.

[ssanfu]

Can you do the same with an Append query?

Yes, I do it all of the time.

Except I use

Currentdb.Execute "DELETE * FROM table1", dbFailOnError

Another example:

 sSQL = "INSERT INTO RecordSources (FormName, ctlType, frmRecSource) VALUES ('" & obj.Name & "', 'Form', '" & tmp & "');"
  '                Debug.Print sSQL
  db.Execute sSQL, dbFailOnError

[jo15765]

So it would be insert into as opposed of append?

[ssanfu]

Try creating a new append query, then switch to SQL View to see what the SQL looks like.

(But yes, the SQL of an append query begins with "INSERT INTO..........")

[jo15765]

Yes, I do it all of the time.

Except I use

Currentdb.Execute "DELETE * FROM table1", dbFailOnError

Another example:

 sSQL = "INSERT INTO RecordSources (FormName, ctlType, frmRecSource) VALUES ('" & obj.Name & "', 'Form', '" & tmp & "');"
  '                Debug.Print sSQL
  db.Execute sSQL, dbFailOnError

This is my full procedure, it seems to execute fine but access will crash EVERY time I run it and the append/insert into is not actually executed. If I break it down piece by piece it is the append/insert statement that causes the bug as the importing the Excel Spreadsheet goes rocket fast.

Do you see any issues with my syntax below? This is Access 2013

Public Sub ImportSpreadsheet()
    Dim strXlsx As String, monthname As String


    DoCmd.SetWarnings False


    monthname = Format(CStr(Now()), "mmmm")
    strXlsx = "c:\tEST.xls"
    DoCmd.TransferSpreadsheet acImport, , monthname, strXlsx, True, monthname & "!"


    Do.Cmd.RunSQL "INSERT INTO [ALL_Data] ( [1], [2], [3], 4, [5] )" _
                            + "SELECT [1], [2], [3], 4, [5]" _
                            + "FROM monthname;"
    DoCmd.SetWarnings True


End Sub

[ssanfu]

Do.Cmd.RunSQL "INSERT INTO [ALL_Data] ( [1], [2], [3], 4, [5] ) SELECT [1], [2], [3], 4, [5] FROM monthname;"

Two things:
1) You REALLY have fields named [1], [2], [3], 4 and [5]???? The Access gnomes can get REALLY peeved when field names begin with a number

2) The command is "DoCmd.RunSQL", not "Do.Cmd.RunSQL" (with a period between Do and cmd).

3) If you Set Warnings OFF, you MUST turn them back on at the end of the sub!

DoCmd.SetWarnings TRUE

This is why I use the "CurrentdB.Execute........" command. The set warnings commands is not needed.


As to the code executing properly, I don't know (because of the field names). But I don't see any other errors.


Good luck