MDB Bloats w/ RefreshLink

[JP]

I have an Access 2000 application that resides at about 100 locations. Each
is a separate, standalone installation consisting of a "program" mdb and a
"data" mdb. The locations are remote and cannot support full-time on-line
connection to a central database (Access or other), nor can they support
full-time on-line connection to a web-based application. The "data" mdb
consists of about 25 tables. All access is DAO.

Every week, the "data" mdbs are sent to a central location (some on Zip
Drives by mule). Each is put in its own folder (they all have the same
filename) and a central application (also Access 2000) links to each set of
uploaded tables in turn to load the data into one set of consolidation
tables. I'm using the RefreshLink command to link to each set of uploaded
tables in turn. Essentially, it looks like this:

For Each tdf in CurrentDB.TableDefs
tdf.Connect = ";DATABASE=" & strFilename
tdf.RefreshLink
Next tdf

where strFilename is the fully qualified name of the uploaded db from which
I'm reading.

My problem is that my central application bloats big time every time this
process runs. It increases by about 8 meg for each "data" mdb that's read.
Given that I have about 100 "data" mdbs to read, that's a real problem.

I tried simply dropping the link and then re-linking through the following
code:

For Each tdf in CurrentDB.TableDefs
sTable = tdf.name
CurrentDb.TableDefs.Delete sTable
DoCmd.TransferDatabase acLink, "Microsoft Access",
strFilename, acTable, sTable, sTable
Next tdf

This pretty much eliminates the bloat, but is significantly slower than the
RefreshLink (it just about doubles the total run time for the process)

Am I doing something wrong with the RefreshLink or is there some way of
running it that will prevent the bloat?

Thanks.

 

[John Nurick]

Hi JP,

Have you tried just building and executing SQL statements for the append
queries you need? It could be almost as simple as this:

For each folder in (list of folders where "data" mdbs arrive)
For each tablename in (list of consolidation tables)
strSQL = "INSERT INTO " & tablename _
& " SELECT * FROM " & tablename _
& " IN '" & folder & "\data.mdb';"
dbEngine(0).Workspaces(0).Execute strSQL, dbFailOnError
Next tablename
Next folder

 

[JP]

John,

Thanks for your reply.

Can I do that without first linking to the tables?

 

[Guest]

What kind of data are you using?

For your question..

YOU ARE LEAVING THE SQL TO THE RUNTIME COMPILER. GOOD
CHOICE BUT SLOW. THERE ARE THIRD PARTY SOLUTIONS TO THIS.
CHANGE THE CODE TO REFLECT EXACTLY WHICH TABLE TO GET THE
DATA FROM, THEN MOVE THE DATA INTO THE NEW LOCATION.
AUTOMATICALLY LIKE YOU ARE DOING WILL BE SLOWER. BE MORE
SPECIFIC TO INCREASE SPEED.

 

[John Nurick]

Yes. In Access Help (not VBA help) go to the table of contents and find
Microsoft Jet SQL Reference, then Data Manipulation Language, then
INSERT INTO statement. In there, there's a link for the IN clause.

John,

Thanks for your reply.

Can I do that without first linking to the tables?