Queries in Excel VBA, delete and insert - please help

[bhavankars]

I am trying to connect to Access database from Excel VBA.
And then delete a row if it exists.
Then insert a row in the table.

I have some code from different examples: I have tried to use it, but unfortunately it breaks at: " With rstEQP.Seek = sEQPID" down below.

Please help!!!


Dim rstEQP As Recordset
Dim sEQPID As Long
Dim sAccName As String

sEQPID = ActiveWorkbook.Sheets("Access Export").Cells(2, 127)
sAccName = ActiveWorkbook.Sheets("Access Export").Cells(2, 1)


Set rstEQP = DB.OpenRecordset("Master")
DeleteRecord rstEQP, sEQPID

rstEQP.Close
DB.Close


isql = "Insert into Master (EQP ID, Account Name) values " & sEQPID & "' , " & sAccName & ") ;"

a = MsgBox("PB: Do you really want to close the workbook?", vbYesNo)
If a = vbNo Then Cancel = True

End Sub

Sub DeleteRecord(rstEQP As Recordset, sEQPID As Long)

With rstEQP.Seek = sEQPID
If rstEQP.NoMatch Then
MsgBox "No EQP #" & sEQPID & " in table!"
Else
rstEQP.Delete
MsgBox "Record for EQP #" & sEQPID & _
" deleted!"
End If
End With

End Sub

 

[Peter van Vliet]

Is your Access object library loaded in VBA?

In VBA select view object browser
Right-click next to question mark
Select references
Tick Microsoft Access

Regards,

Peter

 

[bhavankars]

Thanks Peter, I have done that.

But I am stuck in the deleterecord function. I get an error when it is trying to execute : "With rstEQP.Seek = sEQPID
" line.

Please help!