Automatically Importing Excel data into Access

  • Thread starter Thread starter Haas C
  • Start date Start date
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!
 
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
 
this tab gets updated constantly by various users and I need to import this data into the ClaimsDB table every evening
Click to expand...

Jeff is right, just like the table, thus any updates made in xls. are automated in Access. Data in linked tables too can be manipulated from both ends, but inconsistencies between programs such as formatting can only be done in source files.
 
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
 
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!
 
Haas,

If I am reading your question correctly, you want to VIEW the table from Excel IN ACCESS, correct? I assume you want to do this so you can query it easier.

Tell me what you mean by "the data got corrupted in Excel DUE TO Access". Per my original comment, there shouldn't be any problem with this link, unless you are trying to link anything other than TYPES OF DATA, or mixed types of data in single cells. Delete the entire table from Access (even the headers) and start over with a brand new link, by creating a new table for it. Once you've done this, there should be no problem with manual updates to the table, done either in Excel or Access. Its a two-way street. If you do this correctly, you won't have to do anything anymore, except for varifying the accuracy of the data.

As far as corruption is concerned, I can't think of any cause. Although, you might want to careful about opening the source file (Excel) if someone has the linked DB open. I'm pretty sure macros and events would not be the problem, I have managed transaction logs with automatic macros in Excel and some automation in Access even with the link active and have never had a problem.

Also, what do you mean by this statement....
the table drives a few queries....?
Click to expand...
 
Last edited:
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
 
Back
Top