LosBear
asked on
Deleting a record from MS Access
I pulled up a recordset (RS1) and need to delete all of them except for the first one.
How do I delete the record I am currently viewing in a do-loop. There are no primary keys, indexes, unique id's, etc.
Assume there are two or more IDENTICAL records, but I only want to delete all but one.
Here's my code.
Do While Not RS1.EOF
If i > 0 Then
objConn.Execute("DELETE STATEMENT WOULD GO HERE? OR NO SQL STATEMENT LIKE THIS?")
End If
i = i + 1
DoEvents
RS1.MoveNext
Loop
Thx in advance!!!
How do I delete the record I am currently viewing in a do-loop. There are no primary keys, indexes, unique id's, etc.
Assume there are two or more IDENTICAL records, but I only want to delete all but one.
Here's my code.
Do While Not RS1.EOF
If i > 0 Then
objConn.Execute("DELETE STATEMENT WOULD GO HERE? OR NO SQL STATEMENT LIKE THIS?")
End If
i = i + 1
DoEvents
RS1.MoveNext
Loop
Thx in advance!!!
It sounds to me as if the easiest thing to do is save all the data from the first record in memory (your own variables, class properties, UDT elements, whatever), delete the entire contents of the table, and then do an AddNew with the data you saved.
It would be LOTS LOTS faster than single stepping through your record set and deleting each record after the first. And the SQL is easy, too: DELETE * FROM tblName
It would be LOTS LOTS faster than single stepping through your record set and deleting each record after the first. And the SQL is easy, too: DELETE * FROM tblName
Do While Not RS1.EOF
rs1.delete
RS1.MoveNext
Loop
rs1.update
rs1.delete
RS1.MoveNext
Loop
rs1.update
ASKER
I really like your QJohnson's idea of setting it to a variable, and I'll do that if bob_online can't help me with his somewhat easier solution:
I"m getting an error:
"Current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype"
This is my connection string:
objConn.Open "Provider=Microsoft.Jet.OL
Please help!
Thx guys
I"m getting an error:
"Current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype"
This is my connection string:
objConn.Open "Provider=Microsoft.Jet.OL
Please help!
Thx guys
His solution actually does take less code work on YOUR part. But it is lots more work for the computer if your recordset has any size at all.
Seems like your connection string is ok... so if you're getting a complaint about the recordset look there.
FYI, code for my solution would look like:
Dim rs as ADODB.Recordset
' your code to open objConn goes here
set rs = New ADODB.Recordset
with rs
.ActiveConnection = objConn
.Open "mytablename",,adOpenDynam
If (not .EOF) and (not .BOF) Then
.MoveFirst
' code here to save field values
.Close
objConn.Execute "DELETE * FROM mytablename"
.Open "mytablename",,adOpenDynam
.AddNew
' code here to put field values into new first record
.Update
.Close
End If
end with
set rs = Nothing
' do whatever you need to do with objConn at this point
I can help with the code to save and retrieve the field values from some type of data structure if you aren't familiar with that.
Seems like your connection string is ok... so if you're getting a complaint about the recordset look there.
FYI, code for my solution would look like:
Dim rs as ADODB.Recordset
' your code to open objConn goes here
set rs = New ADODB.Recordset
with rs
.ActiveConnection = objConn
.Open "mytablename",,adOpenDynam
If (not .EOF) and (not .BOF) Then
.MoveFirst
' code here to save field values
.Close
objConn.Execute "DELETE * FROM mytablename"
.Open "mytablename",,adOpenDynam
.AddNew
' code here to put field values into new first record
.Update
.Close
End If
end with
set rs = Nothing
' do whatever you need to do with objConn at this point
I can help with the code to save and retrieve the field values from some type of data structure if you aren't familiar with that.
ASKER
Actually, I set them all to variables, deleted all the records, and ran an insert into off the variables.
Worked great!
Is this cool or does it use a lot of overhead?
Worked great!
Is this cool or does it use a lot of overhead?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx for your help
Is there anything unique about each record ?
How are you opening the recordset ?
If the recordset is opened as dynamic or keyset and is not readonly, then you can simply do RS1.Delete to delete the current record in the recordset.
Otherwise, you will have to build a SQL statement to delete all recordset where all the fields match that of your current record. This will only work if there are no complete duplicates.
SSQL = "DELETE FROM MyTable WHERE sName='Alon' AND sSurname = 'Hirsch' AND dtDOB = '28 Oct 1966' AND ....
HTH,
Alon