[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Deleting a record from MS Access

Posted on 2003-03-20
8
Medium Priority
?
149 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
8 Comments
 
LVL 4

Expert Comment

by:Alon Hirsch
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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

607 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