Importing data in to an Access table.

[TADropik]

I am importing data from a linked table in to an Access table.
I am using queries to do this.

the Linked Table is named Source_Table
the Access Table is named Target_Table

First I run my Delete Query: (DELETE * FROM Target_Table)
Then I run my Update Query: (INSERT INTO Target_Table (field) SELECT
Source_Table field FROM Source_Table)

What is happening is the overall size of the MDB gets bigger and bigger each
time I run these queries. I have this application run automatically and after
a period of time the MDB is huge.

Is there a better method of doing this?

 

[mscertified]

Why 'import' a linked table?
Why not just use it where it is. That is the whole point of a linked table.

There are a few of things you could do to reduce the size. Only import the
columns you need instead of importing all columns, make sure you only have
the columns indexed that you absolutely need, and turn on the 'compact on
close' option.

-Dorian

 

[TADropik]

Great question Dorian
The Linked Table is a table that resides at a remote location and I am
linking to it through and ODBC connection. It is a rather large table and it
just not feasible to run the reports against that table. So I import the data
over night so they can run their reports quickly during the day.

 

[Jeff Boyce]

While you are "deleting" records, Access is not cleaning up after itself.
You will need to periodically use "Compact & Repair" to squeeze out that
unused extra space. NOTE: make a backup of the .mdb file before you run
Compact & Repair!

Regards

Jeff Boyce
<Office/Access MVP>