Import and Append of txt files

[Jorist]

Every month, I receive a series of txt files that are to replace ALL
data in my current database. Each table in the database has the same name as
a corresponding txt file. As such when I import the txt files, the tables
they create are each named tableA1, tableB1, etc..

Once I have the tables imported, I then delete the contents of the
current tables useing delete queries. I then append the contents of tableA1
to tableA, tablenameB1 to tableB, etc., repopulating the tables with the new
data. I then delete tableA1, tableB1, etc. After all of the tables have been
updated, I then run "compact and repair" to reduce the size of the database.

Can anyone suggest an easier way (preferably an automated way) of doing
this. This database has 30 tables and is very large for an access database so
it takes a while to complete. Any suggestions to simplify or automate this
process would be greatly appreciated.

Thanks in advance.

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Every month, I receive a series of txt files that are to replace ALL
data in my current database. Each table in the database has the same name as
a corresponding txt file. As such when I import the txt files, the tables
they create are each named tableA1, tableB1, etc..

Once I have the tables imported, I then delete the contents of the
current tables useing delete queries. I then append the contents of tableA1
to tableA, tablenameB1 to tableB, etc., repopulating the tables with the new
data. I then delete tableA1, tableB1, etc. After all of the tables have been
updated, I then run "compact and repair" to reduce the size of the database.

Can anyone suggest an easier way (preferably an automated way) of doing
this. This database has 30 tables and is very large for an access database so
it takes a while to complete. Any suggestions to simplify or automate this
process would be greatly appreciated.

Answered once but received web communication error so I hope not to repeat.
Instead of importing and appending data try linking to each of the thirty
text files. Once the links are created - replce your old tables with these
linked data sources in all yoru queries etc. Provided the text files deliver
consistant data fromatted the same way each month, as soon as you save the
new file over the old file your new data will be available in your database.
I have many large reports spooled to download to text files every early
morning, they write over the old file every day. When I open the database I
have new data and current reports.