access query re updating info. can I use a macro?

[Guest]

I have created an access database that has 5 tables of data. Each month an
update will be received. The data will be in txt format and be delimited. I
would like to know the easiest way to update the tables each month. The
tables have queries based on them so I don't want to just replace the tables.
The data will completely replace the existing data which may be easier but
I'm unsure how to do this without a lot of hassle. Could I set up a macro to
make this easier so other people could run the macro and therefore do the
update themselves?

A quick response would be appreciated as I only have a day to do this. thanks

 

[Steve Schapel]

Madiziam,

You can use a TransferText action in your macro to import the data into
your database. I guess you would have one TransferText for each of the
5 tables. If the text files are structured in such a way that the
impored data is reliably exactly what you want in your Access tables,
then I would use a DeleteObject action to delete each of the existing
tables, followed by the TransferText actions. The fact that you have
queries based on the tables will not be affected by this, so long as the
tables created by the TransferText have the same names as the tables
deleted. If, on the other hand, the data in the text files needs some
"massaging" to get it in the required shape for your Access tables, then
use the TransferText to import the data into a temporary table, and then
some Append queries to move the data from there into the main tables, in
which case you can use a RunQuery action in your macro to make the
Append happen.