Import Problem

[PSmit]

I am having a problem importing an Excel file into an existing table and can not find any solution in the forum (maybe I missed a posting...).

A group I work with had an Excel file that they imported into an existing table in a DB perfectly fine. They decided to then add more columns to the file and to the table the file imports into. This caused an "file can not be imported" error.

I've been playing with it and can not figure out what is wrong. If I strip down their "added to" file back to the original, it works great. If I go back to the original setup and attempt to add just one new column to both the table and the file, I get the error (even if I make sure they are of the same data type, etc.)

I have even tried importing the new "added to" file into the DB as a brand new table, created by the import. I then removed all of the data it imported and tried to repeat the import, this time telling the DB to import the file into this newly created table. However, I got the same error message as before...

Any ideas out there? Probably just something small and silly that I'm completely missing.

In case it makes any difference, the original file has about 117 columns and the new "added to" one has 137. Didn't know if maybe there is a limit issue that we are hitting there.

Also, I'm using Access 2003, if that makes any difference as well...

Thanks in advance for any and all suggestions~
PSmit

 

[mhartman]

Hello

Try importing both tables separately. Then use an append query in Access to combine both tables. Make sure both tables have the same amount of columns ad field names.

Regards
Mark

 

[DCrake]

Simple Software Solutions

Hi

Using Access import routines rely on import/export specs. if you select Tools > Options and click on the System Objects and click apply you will be able to the Msys tables that contain existing import/export specs

 

[PSmit]

Got it working - although not like I wanted it

Thank you for the replies.

DCrake, I went to Tools -> Options, but didn't find System Objects.

mhartman, I somewhat used your suggestion. I imported the Excel file as a new table in the database. I then wrote queries to delete the data in the table I want my data in, append the data into the table I want my data in, and then delete the new table I first imported the Excel file into. Hooked it all up using a macro and it works great now without any errors or problems.

I still don't understand why Access wouldn't just let me import directly into my original table instead of having to do this whole work-around, especially when it will let me append everything over. But, oh well. It works now!

Thanks to you both~
PSmit

 

[mhartman]

Hello:
I am glad that suggestion worked because that is what I use. Depending on the complexity of your tables, it might be that your asking too much of Access at that time, or end of file, or line characters, or a corrupt table.

Regards
Mark