delete a milion of records ado + vb6

[sal0003]

I use the tipical ado jet con and vb6 code.
I have an access table with approx 4.500.000 records in 43 fields.
All field are filled with value.
I use the delete * from table ecc.... but sure the code is very very slow...
Suggestion please to make a fast way to delete.
tks.

 

[kray4660]

Can you drop the table and recreate it. It would execute a lot faster.

 

[sal0003]

please i'm a new bie....
example code?
tks

 

[sal0003]

in other case the drop option is reserved for Sql Server or not?

 

[kray4660]

We have a Access application that we have used for years where we drop a table and re-create it (it is done weekly).

Example:
DoCmd.RunSQL "Drop TABLE tblBatchInfo"

DoCmd.RunSQL "CREATE TABLE tblBatchInfo(BatchNumber integer, BatchDate datetime,LastChkNum integer)"

The above code drops tabel tblBatchInfo, then re-creates the table with the fields, BatchNumber, BatchDate, and LastChkNum.

I hope this helps.

 

[kjv1611]

The TRUNCATE command can only be used on SQL Server and not Access, but the DROP command can most definitely be used in Access.

If there were a way to use the TRUNCATE command in Access, you'd be in business for sure! That is one super-fast command - faster than dropping and recreating by far!

 

[kjv1611]

Here's what I'd use for the full code in that section, so you don't get a lot of prompts:

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE [blue]MyTableName[/blue]"
DoCmd.RunSQL "CREATE TABLE [blue]MyTableName[/blue](FieldID integer, FieldDate datetime, FieldOther text)"
DoCmd.SetWarnings True

Basically it's the same as what was already suggested, but just adding in the setwarnings statements.

Also, if you've got a large number of fields in said table, then I'd suggest creating the SQL statement ahead of time as a string, and then running that string.... for creating the table.. so something like:

Dim strSQL as String
strSQL = "CREATE TABLE MyTableName(Field1 Integer ,Field2 Text ,Field3 Date, Field4 Date ,Field5 Integer)"

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE MyTable"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Anyway, that should get you going..