Error 424 object required after NULL SQL query

I am running some EXCEL VBA code to update the contents of an ACCESS database table along the lines suggested here: IF @@Rowcount = 0 — SQL Syntax Error in Access . When I execute the SELECT query, EXCEL VBA gives this error message:

Run-time error 424: Object required

I extracted the SQL string from the VBA Watch window and ran it as a Query in ACCESS. The first time I did this, there were no records becasue the table was empty, so I ran the INSERT query in ACCESS and then tried running the VBA code but got the same error message.

The code is here:

Public db As DAO.Database

' Open database

Public Sub OpenMdtDatabase()
    Set db = DBEngine(0).OpenDatabase("SL_MDT_data_v1.accdb")
End Sub

' Update DB table

Sub UpdateDb()

' Initialise

Dim rs As DAO.Recordset
Set xlSht = Sheets("plot_data")

' Open database

Call OpenMdtDatabase

' Get the data to store

sname = xlSht.Cells(6, "R").Value
xfill = xlSht.Cells(6, "S").Value
xedge = xlSht.Cells(6, "T").Value
xstyl = xlSht.Cells(6, "U").Value
xsize = xlSht.Cells(6, "V").Value

' SQL stuff

sqlTxtSelect = "SELECT SeriesName FROM SeriesProperties WHERE SeriesName ='" & sname & "';"

sqlTxtUpdate = "UPDATE SeriesProperties " & _
sqlTxtUpdate = "SET SeriesFill = " & xfill & ", " & _
sqlTxtUpdate = "SeriesEdge = " & xedge & ", " & _
sqlTxtUpdate = "SeriesStyle = " & xstyl & ", " & _
sqlTxtUpdate = "SeriesSize = " & xsize & " " & _
sqlTxtUpdate = "WHERE SeriesName = '" & sname & "';"

sqlTxtInsert = "INSERT INTO SeriesProperties('" & sname & "') " & _
sqlTxtInsert = "VALUES(" & xfill & ", " & xedge & ", " & xstyl & ", " & xsize & ");"

Set rs = db.OpenRecordset(sqlTxtSelect)

If rs.RecordCount = 0 Then
    DoCmd.RunSQL (sqlTxtInsert)
Else
    DoCmd.RunSQL (sqlTxtUpdate)
End If
End Sub

I am guessing that there is something wrong with the SQL SELECT string. I tried setting this directly using

SELECT SeriesName FROM SeriesProperties WHERE SeriesName ='14/10-2:F2F_SLMC'

but still get the same error message. I have also tried removing the colon …

Answer

The problem lies with the statement

DoCmd.RunSQL (sqlTxtInsert)

If I change this to

db.Execute (sqlTxtInsert)

then everything is fine. Should have scrolled to the end of the answer at the original link ….

Advertisement

Answer