Solved: ADO Delete Access Record from Excel Userform

[stapuff]

I have a userform that is returning all the order #'s from a table to a userform combobox. I would like to be able to select an order # then hit a button to delete the record from the table in access. A point in the right direction is all I'm looking for.

Thanks,

Kurt

[Bob Phillips]

Sub DeleteData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "DELETE * From Contacts " & _
"WHERE FirstName = 'Robert'"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

oRS.Close
Set oRS = Nothing
End Sub

[stapuff]

Xld - Thanks for the post. Getting an Runtime error - Data type mismatch in criteria expression. Your thoughts.....

BTW - error occurred on the following line "oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
"

I went back to the access table and made order_# field text. Ran the code which gave me an error "Operation is not allowed when the object is closed" on line oRS.Close.

Thanks,

Kurt

[Bob Phillips]

Kurt,

I assume that you changed the SQL to suit your table, order_# et al.

The oRS.Close was spurious code, I should have removed it as the recordset is not open at that point, so you cannot close it. I adapted this from other code and failed to notice that.

If you do a query, the recordset will still be open, so it is best to clsoe it then, being tidy. Of course, the proper way is to test if it is open



If oRS.State = 1 Then oRS.Close

[stapuff]

Xld -

All is good now. You have taught me something. Everything I have done so far with ADO Access to Excel / Excel to Access has the .Close at the end so it did not even occur to me that it will not open something that is not only not open, but doesnt exist anymore. I appreciate all of your help.

Thanks,

Kurt

[anilkr99]

Hi,
but in this code We can delete only one record at one time.May I request to xld how all records in access database can be deleted using macros.
Thanks in advance.