Solved: Importing Excel spreadsheet into Access problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

KScott

Thread Starter
Joined
Jan 21, 2013
Messages
2
Greetings forum,

I have an Access database main table that I try to update at least monthly with an Excel spreadsheet feed. The spreadsheet formatting is always the same, with occasional additions or deletions of rows...those changes are what I am trying to update the database with. The rows represent personnel and their personal information. People come and go...blah, blah, blah.

The problem is when I run the new reports and save them in Excel. I save them in the same place and rewrite over the old file with the same file name. Then I try to import external data into the Access database using this overwritten file as an append query. It worked the first time I set it up, but when I try to use it again as a saved import, I get an invalid file name for the overwritten file. Microsoft appears to be adding a hidden appendage to the file name when I overwrite the original file.

Has anyone had experience with this problem before?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I haven't heard of this problem.
Perhaps you could tackle it differently. I normally just import the file to a temporary table (which requires no save import) and then use an Append query to add the records to the table and then a delete query to clear the temporary table.
 

KScott

Thread Starter
Joined
Jan 21, 2013
Messages
2
OBP, thanks for the quick reply. I think I went this route because every time I go to do an append or update query I have to manually type in the [table.name].[field.name] for every field in the table I'm appending to, or vice versa, I forget...is that normal or am I doing something wrong? It seems to be a little too laborious.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Once you have set up the temporary table and Append query they should be permanent and you can then use them as often as necessary.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top