Problems with UPDATE and INSERT from DAO

[pbassett]

I wish to check from Excel if a record is in Access and either Update that record or Insert a new record based on the results. The SELECT works fine getting the count, but the INSERT and UPDATEs are giving me an error "Object required"

Here is the code:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim db As DAO.Database
Set db = Workspaces(0).OpenDatabase(myPathname)

Set rs = Nothing
Set rs = db.OpenRecordset(SelectSQL, dbOpenDynaset)
If rs.RecordCount <> 0 Then ' update existing record
DoCmd.RunSQL (UpdateSQL)
Else ' insert new record
DoCmd.RunSQL (InsertSQL)
End If

My UpdateSQL variable is
UPDATE [Planned Resources] SET Jun = 0 WHERE PIW = '261' AND Category = 'Billing Ops'
which runs OK directly in Access.

Thanks,
Pete

 

[bat17]

I think that the problem is that the DoCmd statements are not pointed at Access. Not tested but you could try:-

Dim rs As DAO.Recordset
Dim strSQL As String
Dim db As DAO.Database
Set db = Workspaces(0).OpenDatabase(myPathname)

Set rs = Nothing
Set rs = db.OpenRecordset(SelectSQL, dbOpenDynaset)
If rs.RecordCount <> 0 Then ' update existing record
db.DoCmd.RunSQL (UpdateSQL)
Else ' insert new record
db.DoCmd.RunSQL (InsertSQL)
End If

HTH

Peter