ADO – Delete records (Access 2000)

Topic

New Reply

Dear Loungers,
I have tried (I think) every combination of locktypes and cursortypes, but still Access will not allow me to delete records. It should be so simple. I’m going against a plain vanilla jet data database.

Sub test()
Dim rstPurchaseDetail As ADODB.Recordset
Dim strsql As String
strsql = “SELECT tblPurchaseHeader.*, tblPurchaseDetail.* ”
strsql = strsql & “FROM tblPurchaseHeader INNER JOIN tblPurchaseDetail ON ”
strsql = strsql & “tblPurchaseHeader.PurchaseID = tblPurchaseDetail.PurchaseID ”

Set rstPurchaseDetail = New ADODB.Recordset
rstPurchaseDetail.Open Source:=strsql, ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic

rstPurchaseDetail.Delete adAffectAll
End Sub

Hope you can help,

Gwenda

Reply | Quote

Replies

I’m not sure what you’re trying to do here. You appear to be selecting all the records in two tables with an inner join and then trying to delete them. Why no just use DELETE in the SQL in the first place? There doesn’t appear to be any other purpose to your sub, so why do it the hard way? Create the SQL for a DELETE query, assign it as the commandtext of a command object and then execute it.

By the way, I’ve had trouble with adAffectAll. Its effects are sometimes bizaare. Try adAffectCurrent instead. It isn’t intuitive, but it seems to work.

Reply | Quote

Charlotte-oh-blessed-guru-divine … I can learn from you in 2 seconds what it takes hours to figure out by books. Thank you … I will do as you suggest and let you know how it works out.

Reply | Quote

Thank you Charlotte.

Code below works like a beauty and now I’ll integrate it into my program. BTW, it won’t work if the SQL specifies a join between the header and detail records, but in the Relationships window they are joined with “Cascading Delete” turned ON, so if I delete the header records .. eh voila! the detail is gone too.

Sub test3()
Dim cmdDelete As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection

strSQL = “DELETE tblPurchaseHeader.* FROM tblPurchaseHeader”
Set conn = CurrentProject.Connection
Set cmdDelete = New ADODB.Command
Set cmdDelete.ActiveConnection = conn
cmdDelete.CommandText = strSQL
cmdDelete.Execute

End Sub

Reply | Quote