Importing an Excel file into an Access table

[bearcub]

We have a process where the admins update an excel file and import it into a database that is used once a year.

I need to run a macro when Access initially opens up to import these tables. I don't want to append the existing tables I want to clear out the data and import the infomation for the Excel file

In regards to creating a macro, should I clear the contents of current table before I import the Excel table? I looked at the import options and I don't see where any of them delete the existing contents then copy over the new records.


If i have 7 or 8 tables to import upon opening, would this take a long period of item (the tables are all relatively small tables - less than 100 runs of information).

Thank you for your help,

Michael

 

[Joe4]

You can clear all of the existing information out of your tables quickly and easily with a simple Macro.
Just create a Macro, and add the RunSQL action. And this is the line of code you want for that:

Delete [table1].* From [table1]

Where "table1" is the name of your table.

If you want to do multiple tables at once, just add additional RunSQL actions for each table (can be done all in the same macro).
If you do not like the warnings that you are about to delete data, you can use the SetWarnings action to turn those warnings off.

Then, all you need to do is to click on the macro to run it and clear that data from all the tables you want.

 

[bearcub]

Thank you Joe, this will work. I can create a macro on the open event to clear my tables then import the excel files, correct?

 

[Joe4]

Thank you Joe, this will work. I can create a macro on the open event to clear my tables then import the excel files, correct?

Yep. You can import the files in the same Macro.

 

[bearcub]

Thank you, I was hoping you would say that.