Importing from excel to an existing table

[markvenis]

I am trying to import data from an excel worksheet into an existing, empty, table in Access without success.

The spreadsheet will import as a new table. I have tried deleting the data in the new table created by importing the spreadsheet and then reimporting the spreadsheet into the new table and this will still not work either.

The spreadsheet consists of a combination of numbers and text, i have also tried reformatting the spreadsheet so all values are General rather than text etc but still no luck.

 

[ebkid]

you have a couple of options.

1) delete the table and reimport each time. You obviusly see this works but there are dangers if the format etc changes you could have problems.

2) Create a link to the excel spreadsheet and use that. This can be a problem if multiple epopel need access to the table beacuse this is not aloud with a link to excel.

3) Create a link to the spreadsheet. Create a Make Table query taking all the field from the spreadsheet and so generate a table. Then change the Make Table query into an Append query. That we each time you wish to update the table you just delete the data in the table and run the query which will write the data from Excel in to the exsisting table. If the format changes in Excel you will get an error when you run the query which you can sort out.

 

[SydneyGeek]

A couple of questions:

1. What sort of error messages are you getting? Are they (a) type mismatches, (b) integrity problems, (c) duplicate values in the ID field, or (d) other?
If (c), does the Excel import set contain a column which you are importing into the ID field of the table? If so, you will almost certainly have issues with Access because of duplicate IDs.
If (a), that's one of the joys of getting stuff from Access to Excel. I often use a dummy table, then create an Append query once I have checked the data types to make them compatible with the destination table.
If (b), you are also bringing in IDs to a "Child" table in a relationship (on the Many side), and the "Parent" table lacks some of the IDs. Access has referential integrity rules that will forbid those records from being imported. However, they will probably end up in Import_Errors, where you can go through them and add the appropriate IDs to the "Parent" table.

HTH
Denis

 

[markvenis]

Cheers but I dont think these will work, the problem being that the excel data is a monthly download from another system. If a member of staff left the company in that month their record is not included in the download.

In my existing table I have details of staff that have left and i need to keep them, if i delete the access table and import each time i will lose these records.

 

[markvenis]

Sorry have sorted this problem, was because the column names in excel and access did not match.