Archive of Mr Excel Message Board

John, have you tried importing an Excel spreadsheet into Access (different from converting an Excel spreadsheet to an Access table)? In Access, you select File|Get External Data|Import. You can also use macros in Access to automate your import procedure in Access. I've done this many times, it works great, so if you are looking for any help let me know.

Barrie

I have have tried the Import from Access but it gets as far as Selecting the First Row Contains Column Headings then I select In an Existing Table and select the proper table. At this point the Import Spreadsheet Wizard gives the following error message "An Error Occurred trying to import file A:\test.xls". The file was not imported.

I am using Access 97 and Excel 97 on an NT 4.0 SP6.

Any ideas of what is wrong?

Thanks.

John

Everything you outline seems correct. Have you tried moving the file off the floppy onto the harddrive and then doing the import?

Kev

Yes, I have tried it on the hard drive, and I have tried it on several other computers and I am getting the same error messages. I have rename the files, copied to different directories, etc...

Thanks.

John

John,

I really thought that Kev was on the right with the defective floppy hypothesis.

Lets look again at Barrie's proposal.

You need first to define the tables that you need in Access itself (hopefully you have designed a db in the last normal form). You have also to make judicious choices concerning data types with every field. The (primary) keys must be set too at this stage and necessar relationships between tables established. Once you have everything in place in Access, you can import and/or reimport Excel "tables" into existing Access tables. I'm not sure about this, but I always put a "table" on a separate Excel worksheet.

I just tried a re-import of the table in a worksheet named MEMBERS into the MEMBERS table in Access that already had the same contents. Access complained but imported the data anyway into the MEMBERS table. All was well.
I also created another workbook with new members but with the same column headings as the original worksheet. Access imported these additional records into the existing, already filled MEMBERS table with no problem.

So you might have some problems regarding the definition/design of the tables in Access.

Aladin

Hi again John. Access is extremely fussy when you are importing into it (surprise, surprise). You have to make sure each field (column in Excel) is formatted the same way as the table's field in Access. For example, if a field in Access is text and Excel is treating that column as a number, you will have a type mis-match. You need to ensure the data type is the same. Have a try at that and let me know.

Regards,
Barrie

Thanks to everyone that have offered suggestions and ideas to make things work.

My Access tables are nomalized and field names and data types have been verified. Excel table and its column headings match in order and name to the Access tables but still no success in appending the information to the Access tables. My next step is to start the whole process from scratch (create the Excel Template, create the Excel database worksheet, and create new Access tables) and see if I run into the same problem. I will post the results of this effort later on.

Thanks again for all your help so far.

John

There were several problems that contributed to why it didn't work.

1. There were too many duplicate records.
Because I had to make a flat file from a what was a relational database in order for the Template and Database to interface.
2. Improper data entry (data types didn't match)
Problem was solved when numeric fields were validated in Excel.
3. Access didn't like flat file structure
Created a field (id) to auto number the records as they were imported.