Automatically Importing Excel data into Access without link

[Haas C]

Hi all! I have encountered a repetitive step I must take each and
every time I open one of my Access databases. Here's where I need
help:

I have an access database which when I start it up should delete the
contents of whatever is in ClaimDB table. Next, it should do what I
do
repetitively: Import Data by pointing to an Excel file called 'Claims
Database.xls', from a tab called 'ClaimsDB' - this tab gets updated
constantly by various users and I need to import this data into the
ClaimsDB table every evening. The field names/formats/etc are all set
up in Access and match whatever is in the excel tab and the table
drives a few queries. Remember, I don't delete the table, just the
contents in it, and then I do the import into the same table. Just
want to be able to do all this automatically when I start the access
database.


Please tell me how to achieve the above - I am a beginner at coding
but if you tell me the code and where to put it, I'll make sure to
follow the directions. Please don't tell me to link to Excel because
my spreadsheet got corrupted after a while last time. I just need help
achieving what I've detailed above. Thank you all in advance!

 

[Guest]

To clear the table contents:
CurrentDb.Execute "DELETE * From ClaimDB;"

To import the Excel data, check out the TransferSpreadsheet method of the
DoCmd object. You will probably need to use the 'Range' argument to specify
the tab if it is not the first tab.

You could put it in the OnOpen event of your startup form. I'd probably put
it in a button click event so I had some control over it, otherwise it will
happen every time you close and re-open your db.

If you need help with the TransferSpreadsheet method, just post back.

Steve