[AccessD] Empty and fill tables

Bob,

I don't know if my experience is relevant to your situation, but I too
have a process which imports records into a temp table.  I was loading
data from text files (10,000 to 60,000 records at a time) summarizing
the data, purging, and repeating.  I noticed similar performance
degradation over time, and traced it to db bloat.  My solution
(suggested by Gustav) was to shift the temp tables to a separate back
end, of which I made two copies - TempTables.mdb and
TempTablesTemplate.mdb.  Then at the end of each processing cycle I
included code to kill TempTables.mdb and copy TempTablesTemplate.mdb in
its place.  This ensures that the temp tables always start fresh for
each processing cycle and the bloat problem disappears.  Just make sure
that no processes or db objects have a lock on the tables in
TempTables.mdb when you run the kill and replace part.

Hope this helps

Don McGillivray

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Heygood
Sent: Thursday, October 21, 2004 12:16 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Empty and fill tables


Hello to the list,

I have an app that uses linked/attached tables thru ODBC.
When I created the original, I wrote a routine to empty an Access table
with
info from the corresponding linked table. This because I felt that I
wanted
to not impact the program which uses the table which I was linking to.
The non-Access linked tables are linked to the F End. The Access tables
I
fill are in the back end.
This "empty and fill" operation always took awhile but recently jumped
to 59
minutes.
Five tables -  one over 106,000 record.
A version 2K

Code - - -
EMPTY - -
Dim strSQL As String

DoCmd.Hourglass True
DoCmd.SetWarnings False
strSQL = "Delete * From tblTransactions"

DoCmd.RunSQL (strSQL)

FILL - -

Dim strSQL As String

DoCmd.Hourglass True
DoCmd.SetWarnings False
strSQL = "Insert into tblTransactions Select * From pMWTRN"

DoCmd.RunSQL (strSQL)


DoCmd.Hourglass False
DoCmd.SetWarnings True

My question:
Can I do this faster/better?


TIA,

Bob



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com