Import Excel Worksheet in Access Table [with lookups]

[enfinity]

Hi there,

I'm pretty new to Access so I hope this question is not to simple/stupid

Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:

- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.

- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)

Any idea how I should procede so that I can import my data into Access?

Thanks!
Steve

 

[boblarson]

For one and I'll say it loudly: DO NOT USE LOOKUPS IN TABLES! They will only cause you problems. You do not use tables to directly enter data into them, use forms which you can then set your own lookups using combo boxes, list boxes, etc.

This is one example of why you do not use lookups in tables. They do not work well for most things and will only cause you pain and suffering in the long run. Why Microsoft put them in is anyone's guess but they shouldn't have.

 

[enfinity]

Thanks Bob. I figured that lookups are prone to produce failures... but I have to work with this shitty Access db... any ideas how I can get the data into the db?

Steve

 

[boblarson]

With lookups in there and trying to import data, I'm going to guess manually by hand. But, you can try importing into a new table and then using an append query. Not sure if that will work as there is no way, that I know of, to associate the lookups via an import.