Access adds new table when importing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in a hospital lab. One of the analysers we use saves test data in a
new access database for each day.
I've written a macro to import the database into a user interface. When it
imports the new DB it duplicates the tables with the new data and adds a
number to the table name, e.g Specimen, Specimen1, Specimen2...

I have had to add steps to the macro to rename and delete the duplicated
tables, is there any way to avoid this,
 
It sounds as you have built your macro to import the tables from the
"daily" database. Instead, you can create append queries that get the
*data* as opposed to the tables.

I find that the simplest way to do this is to create a new query and
switch to SQL view, then type in a basic SQL statement like so

INSERT INTO xxx
SELECT *
FROM yyy IN 'D:\Folder\Sub folder\Daily Database.mdb';

where xxx and yyy are the names of the destination and source tables
respectively, and using the actual path to the file.

If you then switch the query back to Design view you can fine tune it as
needed before saving it.

Once you've created the queries, modify your macro so it runs them.

After that, it's just a matter of moving and renaming each day's file so
the queries can find it, and then running the macro.
 
Back
Top