Deleteing Recordset

[scottrod]

Only knowing enough to get myself in trouble, I'm trying to delete records from a recordset. I'm writing the code in Excel97 against an Access97 database using an ADO connection. I populate a table with data from one of the Excel worksheets. This data contains certain records that need to be removed. I generally took care of the removal with a DO...If Then ElseIf... Loop in Excel. My thought from a project flexibility viewpoint was to create a list in Excel, loop through the list passing the information as the WHERE in a SQL statement such as:
mySQL = "SELECT * From IntrFac WHERE NameID='9650'"
substituting my variable where I've hardcoded 9650. The resulting recordset could be none or any number of records.
I want to delete anything I find.
My section of code looks like this:
Sub DumpEm()
Dim connADO As ADODB.Connection
Dim rsADOIntrFac As ADODB.Recordset
Dim strConn,mySQL As String

'Open connection
Set connADO = New ADODB.Connection
strConn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Work\Spreadsheets\Intrafac\intrfac.mdb"
connADO.Open strConn

Set rsADOIntrFac = New ADODB.Recordset
'Using 9650 for testing purposes
mySQL = "SELECT * From IntrFac WHERE NameID='9650'"

rsADOIntrFac.Open mySQL, connADO, adOpenKeyset, adLockOptimistic
If rsADOIntrFac.RecordCount <> 0 Then
'records exist for that period, we need to update so delete the recordset
rsADOIntrFac.Delete
rsADOIntrFac.Update
End If

rsADOIntrFac.Close
Set rsADOIntrFac = Nothing
connADO.Close
Set connADO = Nothing

End Sub

With all this said, my question is this: I had to change the cursortype to adOpenKeyset from adOpenDynamic to be able to check the RecordCount. Is there a better way to handle this? The Delete statement deletes the first record of a recordset, I found out by default. Do I have to loop through the recordset until EOF and delete the records that way? It would be nice to delete everything in one swell foop.

Thanks for everyone's consideration,
scott.rodney@tenethealth.com

 

[dpaulson]

You would need to loop though each record to delete. The update part is not required. You could just use an sql as follows.

mySQL = &quot;DELETE From IntrFac WHERE NameID='9650'&quot;
myConnection.Execute mySQL





David Paulson

 

[scottrod]

Thanks for the response. I forgot about DELETE.
Once again, thanks.