MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Append Excel Records to Access Database

Posted by John on May 29, 2001 11:41 AM

I have setup a database in Access and would like to import records from an Excel spreadsheet.

1. Have setup a template to create records in an excel database file.
2. Would like to either export the records to Access then delete the records in Excel
3. Or retrieve the records from Access and then delete the records in Excel.

Problem: The Excel database is updated by various users and not all of them have
Access installed. That's why I have used the Template in Excel to gather the data in Excel.

Only one user will have Access installed. He/she will be responsible for gathering all the data and create reports from the database.

At the moment, I am able to use the facility from Excel "Convert to Access" to create a new table in Access but it gives me error messages when I try to import the data into an existing table even though the table itself (created by first import, as well as creating a blank table with identical fields to the Excel Database).

Can this be accomplished through macros and if so please provide some advice on this.



Posted by Barrie Davidson on May 29, 2001 11:45 AM

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.


Posted by John on May 29, 2001 12:34 PM

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?



Posted by Kevin James on May 29, 2001 12:46 PM

Defective floppy?


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


Posted by John on May 29, 2001 1:03 PM

Re: Defective floppy?

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...



Posted by Aladin Akyurek on May 29, 2001 1:32 PM

Import to Access


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.


Posted by Barrie Davidson on May 29, 2001 1:34 PM

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.


Posted by John on May 30, 2001 6:01 AM

Re: Import to Access

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.


Posted by John on May 31, 2001 12:03 PM

Re: Import to Access - Problem Resolved

Problem has been resolved. Thanks for all the help you have provided.

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.