Temp tables & dev strategy (Access 2002)

[John]

I have an Access 2002 db where I need to update tables either from external
data (Excel spreadsheets) or from other tables/queries in the db. It seems
to make sense to use temporary tables as an intermediate step. However, I am
self-taught, so I am not sure what the "proper" approach to using temporary
tables is. Listed below are the strategies I can think of or have seen
suggested. I have been using the first two (mostly the first one), and have
issues with data type conversion (with external data) and database bloating,
so I am trying to learn better ways. Can anyone tell me about the advantages
/ disadvantages of these strategies, or what is considered good development
practice? Are there other better strategies I am missing?

1) Use a make table query (or DoCmd.TransferSpreadsheet for external data)
to create a new temporary table. Work with data from the new temporary
table, then delete it when done.

2) Create a temporary table from code, then use an append query (or
DoCmd.TransferSpreadsheet for external data) to append data to the temp
table. Work with data from the new temporary table, then delete it when done.

3) Create a permanent "temporary" table in the database. Delete any data
from the table, then use an append query (or DoCmd.TransferSpreadsheet for
external data) to append data to the "temp" table. Work with data from the
"temp" table, then delete the data when done, leaving the table structure.

4) Create a temporary mdb file and import the data to a table in the temp
db. Link to the table in the temp db from the main db. Work with data from
the linked table, then delete the temp mdb file (and possibly the linked
table) when done.

Thanks,
John

 

[George Nicholson]

For what its worth:

Assuming that the data being imported is in a static format, I personally
tend to use #3 most frequently. I import data into an existing "shell" table
that has very few constraints (to eliminate import errors). I then
modify/update that data as necessary and then run an append query to add
that new data to my "real" data, then empty the shell. DataType changes can
happen in either the modification stage or during the append. One advantage
to having an empty "shell" in the mdb is that my append query (and any
update queries) refers to a real table, not a table that comes-and-goes out
of existence. This way 1) if I run Documentor or Access Analyzer (or VBA
compiler), I won't generate "object doesn't exist" errors and 2) the queries
are always editable (which is difficult/impossible if a referenced table
doesn't happen to exist at the moment).

It also avoids creating objects at runtime, which I try to avoid as much as
possible since that can become an issue if I need to distribute a runtime
version.

HTH,

 

[John]

Thanks, George, that is helpful. The data is in a static format. One of the
big disadvantages of #1 and #2, as I have discovered, is that my database
gets bloated quickly. There is only one copy and only a couple of users, so
it's not a huge deal to manually compact it periodically, but it would be
nice to not have to do it so often. Does #3 get around that issue, or do I
have to go to #4 (or some other strategy)?

Thanks,
John

 

[George Nicholson]

If you are importing and deleting data, I think you will be facing a bloat
issue regardless of whether you create/delete tables or just the records
within them. I would be surprised if there was any way around that, but
that is just a gut feeling.