?
Solved

Deleting a record from MS Access

Posted on 2003-03-20
8
Medium Priority
?
147 Views
Last Modified: 2010-05-01
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!!!
0
Comment
Question by:LosBear
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:AlonHirsch
ID: 8174523
Hi,

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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8174538
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
0
 
LVL 5

Expert Comment

by:bob_online
ID: 8174551
Do While Not RS1.EOF

rs1.delete
RS1.MoveNext
Loop
rs1.update


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:LosBear
ID: 8174873
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.OLEDB.4.0;Data Source=c:\db1.mdb"

Please help!
Thx guys
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8175315
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",,adOpenDynamic ,adLockOptimistic,adCmdTableDirect
      If (not .EOF) and (not .BOF) Then
            .MoveFirst
            '     code here to save field values
            .Close
            objConn.Execute "DELETE * FROM mytablename"
            .Open "mytablename",,adOpenDynamic ,adLockOptimistic,adCmdTableDirect
            .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.
0
 
LVL 1

Author Comment

by:LosBear
ID: 8175449
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?

0
 
LVL 3

Accepted Solution

by:
QJohnson earned 500 total points
ID: 8175648
No, that was a fine solution.  
0
 
LVL 1

Author Comment

by:LosBear
ID: 8175761
Thx for your help
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question