Initialize/Clear DB Table

[Cybernetic2002]

I need some way of clearing all of the data from a table in my database.

What I have is a time collection system which needs to have it's collection table cleared every time the merging program is run. I am using ADO with an ODBC source called "TCMerge".

Here is my current code:

Dim connTimeColl As New ADODB.Connection
Dim cmdTimeColl As New ADODB.Command
Dim rsTimeColl As New ADODB.Recordset

Dim strcmdString As String ''SELECT' command variable

strcmdString = "SELECT LocationWand, EmployeeID, RecordType, JobID, TaskID, TCTime, TCDate FROM TimeColl"

connTimeColl.Open "DSN=TCMerge;uid=;pwd=;database=TCMerge" 'open dataconnection
Set cmdTimeColl.ActiveConnection = connTimeColl
cmdTimeColl.CommandText = strcmdString
rsTimeColl.CursorLocation = adUseClient
rsTimeColl.Open cmdTimeColl, , adOpenStatic, adLockBatchOptimistic

rsTimeColl.MoveLast
rsTimeColl.MoveFirst

Do Until rsTimeColl.EOF
rsTimeColl.Delete
rsTimeColl.UpdateBatch
rsTimeColl.MoveNext 'move to next record
Loop

connTimeColl.Close

However, when I run the code it deletes the first 8 or so records then I get the following error:

"Run-time error '-2147217864 (80040e38)':
Row cannot be located for updating. Some values may have been changed since it was last read.

All I need is some simple code to clear all the data in the table. I have already tried using a 'DELETE' SQL statement instead of a 'SELECT' statement with no luck. Any ideas anyone? Also, if anyone knows how to compact an Access database using ADO in VB let me know.

Thanks in advance

Cyb.

 

[stoolpigeon]

I'm not sure what kind of database you have- but find out what sql statement you need to do the equivalent of "Delete * from table" and open that. This will be quicker and work better than opening a recordset and looping through it.

If you have sql server it would look like this:

strcmdString = "DELETE TimeColl"

* all your connection stuff. from above *

and then open the recordset. You don't need the command object either if are using the recordset. You could open the connection and open the recordset.

The recordset will open, execute the query and close.