Delete Records from Code using a recordset

[mrussell71]

This code as been giving me trouble for a number of days. I've tried different variations to no avail. What I am trying to accomplish is automate a large number of DELETE queries by creating a recordset for each query's SQL SELECT statement, delete all records from that recordset and then move on to the next set of data to be deleted. I am using Access 2002.
The code runs as expected but the data in the underlying tables is still there. It's as if the records are getting deleted but the table isn't being updated from the recordset. However, when I step thru the code, I don't see the [tt]Do While...Loop[/tt] being used, so I'm not sure if the [tt]curRST.Delete[/tt] is being called. It jumps from the [tt]Do While curRST.EOF = False[/tt] line to the [tt]curRST.Close[/tt].

Here is the code that I am using:
[tt]
Dim curConn As New ADODB.Connection
Dim curRST As ADODB.Recordset
Dim strSQL As String

Set curConn = New ADODB.Connection

With curConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source=" & "\\EPC\WorkingDBs\EPC_working.mdb"
.Open
End With

strSQL = "SELECT DISTINCTROW tblEPC_query.ID, tblEPC_query.MfrCode, tblEPC_query.PartNo, tblEPC_query.Label, tblFootnotes_query.Description, tblIndex_query.EPCIndex, tblIndex_query.* "
strSQL = strSQL & "FROM tblFootnotes_query INNER JOIN (tblEPC_query INNER JOIN tblIndex_query ON tblEPC_query.ID = tblIndex_query.EPCIndex) ON tblFootnotes_query.ID = tblEPC_query.FNIndex "
strSQL = strSQL & "WHERE (((tblEPC_query.MfrCode)='A1C') AND ((tblEPC_query.PartNo) Like '18%') AND ((tblEPC_query.Label) Like '%CALIPER%') AND ((tblFootnotes_query.Description) Like '%Semi-Loaded Caliper%') AND ((tblIndex_query.EPCIndex)=[tblEPC_query]![ID]));"

Set curRST = New ADODB.Recordset
Set curRST.ActiveConnection = curConn
curRST.CursorType = adOpenDynamic
curRST.LockType = adLockOptimistic
'MsgBox strSQLa
curRST.Open strSQL

Do While curRST.EOF = False
curRST.Delete
Loop

curRST.Close[/tt]

Is there anything that I am missing here??

 

[kevinclark]

I can not tell from quickly looking at the SQL statement but if it is jumping from "Do While curRST.EOF = False" straight to "curRST.close" then the End of File condition must be evaluating to true - thus no records in the recordset to delete.

Also I think you need to move to the next record at each instance of the loop:

Do While curRST.EOF = False
curRST.Delete
curRST.MoveNext
Loop

 

[mrussell71]

Kevin,
How can I tell whether or not the recordset is returning any records? I threw a [tt]MsgBox curRST.RecordCount[/tt] before the Do While curRST.EOF = False line and it is returning a "-1".

Not sure what to make of this.
Thanks for your help

 

[kevinclark]

Some Providers and or CursorTypes won't return a valid RecordCount. I can not imagine the provider you are using as causing a problem so try changing the CursorType to adOpenKeyset and see if that will give you a count.

Also you may have to actually navigate to the last record and then back to the first before it will give you a count

Example Changes:

...
curRST.CursorType = adOpenKeyset
curRST.LockType = adLockOptimistic
curRST.Open strSQL


curRST.MoveLast
curRST.MoveFirst
Msgbox curRST.recordCount - 1

Do While curRST.EOF = False
curRST.Delete
curRST.MoveNext
Loop

curRST.Close