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. Also, please don't tell me to link to Excel
because that seems to cause corruption in the Excel file from time to
time. Thank you all in advance!

 

[Guest]

Not that hard to do. See steps below:
--
Dave Hargis, Microsoft Access MVP

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.

If your application does not have either an Autoexec macro or a startup form
identified, then you will need one. If you will actually do this every time
you open the databse. Here is the way to delete the data in a table:

'Delete the contents of the table
Currentdb.Execute("DELETE * FROM MyTable;"), dbFailOnError
'Import the data into the table
DoCmd.TransferSpreadsheet, acImport, , "MyTable", strFilePath, True,
"ClaimsDB"


Next, it should do what I