Automatically Importing Excel data into Access

H

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. Thank you all in advance!
 
J

Jeff Boyce

If the Excel data is "live", why import a copy? Can you simply link to the
Excel file and see the data real-time?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
C

Chris Reveille

Link to the Excel file
Run a delete query to delete the records in your table
run an append query to add the records from the linked Excel spredsheet to
you accass table
 
H

Haas C

Link to the Excel file
Run a delete query to delete the records in your table
run an append query to add the records from the linked Excel spredsheet to
you accass table
--
Chris







- Show quoted text -
Click to expand...

Problem is, I tried to do the link to Excel, but data in Excel got
corrupted due to Access. Don't want that to happen again. So, with
that being said, can someone please help with my original question or
tell me how to ensure that the Excel file doesn't get messed up again?
Thanks!
 
J

Jeff Boyce

How did you do that before? Which versions of Access & Excel?

I haven't run across a problem like you describe, but have you searched
on-line, or checked the Microsoft Knowledge Base?

And again, if you only need to see the data, not change it, use a query
against a linked table.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top