flushing large tables

[Tcs]

I'm not sure this is the correct group, but since I'm using VBA to do my work,
I'll start here.

I have a table that I expect to contain several million records. Using a query
to delete them takes longer than I'd like. (But I'll live with it, if
necessary.)

Is there any "rule of thumb" one should use as to whether or not one should use
a query to delete the records within a table, vs dropping the table and
re-creating it? (Or can I even DO that?) I'd really rather NOT drop &
re-create it, but I CAN, if I need to.

Any thoughts/suggestions gladly welcome. Thanks in advance,

Tom

 

[John Vinson]

Is there any "rule of thumb" one should use as to whether or not one should use
a query to delete the records within a table, vs dropping the table and
re-creating it? (Or can I even DO that?) I'd really rather NOT drop &
re-create it, but I CAN, if I need to.

Any thoughts/suggestions gladly welcome. Thanks in advance,

Either method - deleting a million rows or dropping a table - will
have some very substantial overhead, and will require that you Compact
your database frequently. Neither deleting records nor deleting a
table will recover the space occupied by those records.

If this is truly a temporary table that you fill, use, and then empty,
consider storing it in a separate backend database containing ONLY
this table. Using the CreateDatabase method to create a new .mdb file,
and the Kill command to delete it, may be more efficient than either
of the above!

John W. Vinson[MVP]
(no longer chatting for now)

 

[Tcs]

Either method - deleting a million rows or dropping a table - will
have some very substantial overhead, and will require that you Compact
your database frequently. Neither deleting records nor deleting a
table will recover the space occupied by those records.

I've had this discussion recently. I knew I wouldn't get the space back. I've
checked the box to "compact on close". It takes several minutes, but I can live
with it.

If this is truly a temporary table that you fill, use, and then empty,
consider storing it in a separate backend database containing ONLY
this table. Using the CreateDatabase method to create a new .mdb file,
and the Kill command to delete it, may be more efficient than either
of the above!

Wow. Now THIS I didn't think of. (I didn't know one COULD.) This sounds VERY
appealing. I assume that I would create the database, then create a link to the
table within. Anything "special" to worry about?

John W. Vinson[MVP]
(no longer chatting for now)

Thanks a lot. I'm really liking the idea of the separate .mdb.

Tom

 

[John Vinson]

Using the CreateDatabase method to create a new .mdb file,

Wow. Now THIS I didn't think of. (I didn't know one COULD.) This sounds VERY
appealing. I assume that I would create the database, then create a link to the
table within. Anything "special" to worry about?

Not particularly. Your analysis is exactly correct; you can use the
CreateDatabase method of the DBEngine object to create a .mdb file,
and then either create the table using the CreateTable method on the
database object, or run a MakeTable query, or use TransferDatabase to
copy an empty template table into the new database.

John W. Vinson[MVP]
(no longer chatting for now)