Delete Records in a Table

  • Thread starter Thread starter Mark White
  • Start date Start date
M

Mark White

I have converted a reasonably complex 97 Db to 2000, and
all is going reasonably well. My only hurdle is in the
code for deleting records in a table.

The Db is used to import excel (CSV) data into table,
compare it to last months table and update only the
changed information. Once this step is done we delete from
the old table called "Latest Claims". The code is:


Set UpdateSet = MyDb.OpenRecordset("Latest Claims")
With UpdateSet.MoveFirst
Do Until .EOF = True
.Delete
.MoveNext
Loop
.Close
End With

Any help with this would be gratefully appreciated

Thanks in advance

Mark
 
without a little more insight into the app, make sure you have set
references (in vb code window) correctly (ADO vs DAO vs compatibility
library, etc.) there is a KB article or two describing migration of code
ESPECIALLY between 97 and 2000...make sure THAT stuff is taken care of....

e.g.

KB 225962
KB 199064
KB 208769

(that should get you through the code compatibility door)

Also, it is my understanding that it is wise to include a .update after each
deletion. I generally do that just to be safe and for robustness:
Set UpdateSet = MyDb.OpenRecordset("Latest Claims")
With UpdateSet.MoveFirst
Do Until .EOF = True
.Delete
Click to expand...
.MoveNext
Loop
.Close
End With
Click to expand...


--

geez....hope this helps.



Frank Bachman
(Grumpy Aero Guy)
 
I have tried using a SQL statement: "DELETE * FROM [Latest Claims];" which works quite fast and does the job. However I end up with an error message: "Process failed because: Update or CancelUpdate without AddNew or Edit"

Any help gratefully still sough

Mark
 
Tell you what.... I'd create a subroutine in a module, use it to loop
through the table/query (like in your code posted earlier) and drop/delete
the "obsolete" records. You can call the sub anywhere, anyhow, from any
event.

This may not be elegant, but in the past I have created a yes/no field in
tables, and whenever the record gets updated, set the table field to "yes".
Then go through and delete all the records with the field checked or
whatever (simple if statement algorithm).

You could then re-loop the table and reset all fields to "no", to be ready
for the next time.

??
 
I have tried using a SQL statement: "DELETE * FROM [Latest Claims];"
which works quite fast and does the job. However I end up with an
error message: "Process failed because: Update or CancelUpdate without
AddNew or Edit".
Click to expand...

.... but if you are using the SQL command, there is no need to go anywhere
near an .Update method... What _exactly_ triggers this error message?

Tim F
 
Back
Top