Solved

Deleting all of the records in a table

Posted on 2002-07-19
20
483 Views
Last Modified: 2008-02-01
    This little routine was intended to delete all of the records in the file, and it seems like it ought to work, but it doesn't:
 
rstDBErrors.MoveFirst
Do Until rstDBErrors.EOF
   rstDBErrors.Delete
   rstDBErrors.MoveNext
Loop
 
     The routine deletes exactly two records every time, then hits .EOF, though there are 14 records in the file.
 
     I can make it work by changing the .MoveNext to .MoveFirst, so that the routine that works is:
 
rstDBErrors.MoveFirst
Do Until rstDBErrors.EOF
   rstDBErrors.Delete
   rstDBErrors.MoveFirst
Loop
 
     What troubles me is that I don't understand why the .MoveNext version doesn't work. Can anyone tell me?
 
     While we're at it, surely there's an easier way to delete all of the records in a table? I tried using adAffectAll and the other options, but all of them gave me run-time error 3219, "Operation is not allowed in this context."
0
Comment
Question by:GebhartBob
  • 5
  • 5
  • 4
  • +4
20 Comments
 
LVL 1

Expert Comment

by:vim_asteya
ID: 7166272
did u try out as

rstDBErrors.MoveFirst
Do Until not rstDBErrors.EOF
  rstDBErrors.Delete
  rstDBErrors.MoveFirst
Loop
0
 
LVL 2

Accepted Solution

by:
priya_pbk earned 200 total points
ID: 7166288
Easiet way is to just delete it without the recordset, like this..

Dim con as new Adodb.Connection

Private Sub Form_Load()
con.Open "......"  'your connection string from sql or mdb
End Sub

Private Sub
Dim strDelete$
strDelete="Delete from Table1"
con.execute strDelete
End Sub

That's it. You dont need to go thru a recordset if you have to delete or update at all if you are deleting or updating a chunk of records with or without a creteria (ie where clause)

Hope this helps!

-priya

0
 
LVL 2

Expert Comment

by:yongyih
ID: 7166409
How about try to move last record first,then move first record then start delete all those records.

rstDBErrors.MoveLast
rstDBErrors.MoveFirst
Do Until rstDBErrors.EOF
  rstDBErrors.Delete
  rstDBErrors.MoveNext
Loop

From what i know, sometimes recordcount didn't store correct value.  we have to move last and move first so that it return correct number of records in recordset.

just try.. good luck. ^_^
0
 

Expert Comment

by:KetanPatel
ID: 7166434
interesting problem buddy !!

To find an answer to this mystery, try to put some light in the following area.

Are you using a keyset cursor type ?? If not than try using the same and find out the results.

What is your cursor type, i hope it is not adlockbatchoptimissitic !!

The above param. can make some diff. in the situation.

ke.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7167116
KetanPatel,

I can only assume that you hit the answer button in error.  Otherwise I cannot explain why you would lock a question with a non-answer.  Besides you have been here long enough to know that it is not appropriate to lock questions, otherwise I would suggest you re-read the EE Guidelines at (http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp)

Thanks,
Anthony
0
 

Author Comment

by:GebhartBob
ID: 7167337
Further info on problem:
 
     The DBErrors table was opened like this:

   rst.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb"
   rst.Source = DBErrors
   rst.CursorLocation = adUseServer
   rst.LockType = adLockOptimistic
   rst.Open options:=adCmdTableDirect
 
     The main thing that's troubling me is that I must be misunderstanding something very basic indeed. In the following (admittedly not-working) code:
 
rstDBErrors.MoveFirst
Do Until rstDBErrors.EOF
  rstDBErrors.Delete
  rstDBErrors.MoveNext
Loop
 
     The .Delete deletes the current record. The .MoveNext gets the next record, and will hit EOF if there are no more records to get. What could be simpler than that. And yet it just doesn't work. I'm not so much interested in a work-around as I am in understanding the underlying cause of the failure of the routine to do what I expect it to do.
 
     Thanks very much, everyone, for your interest and your help.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7167554
The quickest and simplest way to delete all records:

SQL = "Delete * FROM [YourTable];"

with an open ADO connrection:

CN.Execute SQL

Or same sql with a DAO database

DB.Execute SQL

Note:

It you are using SQL Server there is a faster way to do it using

SQL="TRUNCATE TABLE '[YourTable]'"

CN.Execute SQL
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7167739
inthedark,

did'nt I just suggest that :)

-priya
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7168186
priya_pbk you may have said it but I added that it worked with DAO too and that if back-end was sql server there was a quicker way. But your syntax may not have worked with some tables names. And we both failed to indicate that the syntax could have included a Where statement.

SQL="Delete * From [Your Table Name] Where (([YourField]='Something') Or ([Your Numeric Field]=1234) Or [Your Date Field] <= DateSerial(2002,12,31)))"

Please don't feel any offense, just trying to find a bug-free comprehensive answer.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7168191
Which reminds me - if your sql statement includes a where please remember that some O/S (Novell) limit the number of locked records you can have - so on Novell don't delete more that 1000 records per statement.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168233
inthedark,

GebhartBob is using Access.

Anthony
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7168317
Yes error 3219 tells us that, but the point is, if you stay close to ANSI 97 SQL Syntax up/down scaling between SQL Server, Access, etc. is easier.  Just trying to suggest that its always best to write code which is transportable.  But I'll be happy to go with any way thats better.

p.s. I'm only feeling extra sensitive about SQL syntax as I had to convert a system from SQL Server to Access a couple of weeks ago.  I was surprised to find that statments like:

Select * from Name Where name.Number=1234

and

Select * from Products Where Size=10

Worked in Access and SQL server but not through ADO to an Access database the error message was so unhelpfull, it took a while to resolve. So now you all know why I was trying to suggest that a newbie to SQL statements should consider the full impact taking short-cuts with SQL syntax.
(Where Size matters and [Size] does not.)


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168608
inthedark
>>Yes error 3219 tells us that<<
Actually, the following line was the clue <g>:
 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb

But your point is well taken.  Unfortunately, the SQL version in Access is a somewhat crippled version of ANSI SQL-92 and does not support commands such as Truncate Table.  Even if it did, a Truncate Table command cannot be issued if the table is referenced by another table, You have to drop or disable the constraint first.

Anthony
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7168650
>>Please don't feel any offense,
No, Nothing at all!!

>>just trying to find a bug-free comprehensive answer
that's why we all are here :)

-priya
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168680
inthedark,
>>SQL = "Delete * FROM [YourTable];"<<
>>SQL="Delete * From [Your Table Name] Where (([YourField]='Something') Or ([Your Numeric Field]=1234) Or [Your Date Field] <= DateSerial(2002,12,31)))"<<

I forgot to mention something that illustrates the complexities (I would say impossibility) of maintaining compatibility between different versions of SQL.  The following syntax works in Access:
Delete * FROM Table1

But fails in T-SQL with Incorrect syntax near '*'.  The correct syntax on SQL Server is:

Delete FROM Table1

Which incidently is acceptable in Access as well (go figure).

And of course DateSerial is an VBA thing.

Anthony
0
 

Expert Comment

by:KetanPatel
ID: 7169120
Gebhartbob,
The following code gives the problem exactly as you said, comes out of loop after deleting two records.

Private Sub Form_Load()
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\invoice.mdb"
rst.Open "mastClient", , adOpenDynamic, adLockOptimistic, adCmdTableDirect

rst.MoveFirst
Do Until rst.EOF
 rst.Delete
 rst.MoveNext
Loop

End Sub

But if you change the CursorType property from adOpenDynamic to adOpenKeyset the same works perfectly well. The reason is adOpenDyanmic is not able to fetch the correct no. of records and reports invalid status of EOF.

KetanPatel.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7169613
KetanPatel,

Do yourself a favor and make the following change to your example (I think you may find it educational":

Private Sub Form_Load()
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\invoice.mdb"
rst.Open "mastClient", , adOpenDynamic, adLockOptimistic, adCmdTableDirect
' Add the following lines
If rst.CursorType <> adOpenDynamic Then
   Debug.Print "Oops, I did not know that"
    If rst.CursorType = adOpenKeyset Then
        Debug.Print "Wow, the things I learn at EE"
    End If
End If
' Up to here
rst.MoveFirst
Do Until rst.EOF
rst.Delete
rst.MoveNext
Loop

End Sub

This is because Jet does not support a CursorType of adOpenDynamic, but instead degrades automatically (in this case) to adOpenKeyset

So what I am trying to get across to you, is that there is no difference between
rst.Open "mastClient", , adOpenDynamic, adLockOptimistic, adCmdTableDirect

and:
rst.Open "mastClient", , adOpenKeyset, adLockOptimistic, adCmdTableDirect

Anthony
0
 

Expert Comment

by:KetanPatel
ID: 7171127
Anthony,

So what I am trying to get across to you, is that there is no difference between
rst.Open "mastClient", , adOpenDynamic, adLockOptimistic, adCmdTableDirect

and:
rst.Open "mastClient", , adOpenKeyset, adLockOptimistic, adCmdTableDirect

Its certainly true that Jet doesn't support adOpenDynamic, but the problem that our friend is facing is reflected with the first statement with adOpenDynamic, and if you open the recordset with adOpenKeyset, the code works just fine, and that was the point.

You may focus on the issue as to why does it doesn't work even when it degardes to adOpenKeyset !

KetanPatel.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7171310
Here's an idea for you GebhartBob. You may already know this; In Access when you design a query, you can click on View SQL, then copy and paste into your VB App. You have to make a few changes if you are using " within the sql.

So in future, create your delete query, or any other query, using access then paste into your program.




0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7171325
GebhartBob, can we get the answer evaluated, dont you think its been here for couple of days now!!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now