Importing data to a combo box field

[humtake]

Hey experts,

I have a simple import button that imports an Excel file. It's a simple table with headings and everything works fine except for one field.

One of my combo fields is causing an error. All other data is importing fine, even other combo box fields. The only difference with the one that isn't working is that the "Allow Multiple Values" is enabled.

Shortened list of Fields as an example:
Reference_ID (text), Control Activity (Memo), Status (Text), Regulating Body (Text)

The Status field and Regulating Body field have a Display Control of "Combo Box". Status works, Regulating Body doesn't. I've checked the Excel file and both fields are set to General data type.

I get the general error "MS Access was unable to append all data to the table." It is due to a key violation. The Regulating Body field does get its information from tbl_Regulatory. I don't understand why it won't import when the field contains one of the correct values from tbl_Regulatory. For example, one of the options in the combo box is SOX. In the Excel spreadsheet that I'm trying to import, SOX is correctly listed. Why would the fact that the field is based on a primary key of another table cause it not to work even though I'm putting in a valid value? Is there a way to get this import to work?

Thanks all!

[aytee111]

Importing from Excel causes all kinds of problems. Access decides on the format of the data, it doesn't matter how it is defined in Excel, and Access takes the first few rows and decides what the data type is of each field. Memo fields also cause problems - if the first few lines in Excel are less than 256 characters then Access imports it as text and anything longer than 255 is truncated.

It sounds like you are importing directly on to your table - that will work sometimes. Importing into a temp table will allow you to fix the data prior to appending it to your main table - but watch out for that memo field.

[humtake]

Thanks again aytee. In my research people have mentioned using a temp table so I guess that's what I'm going to have to do.

What would you suggest the data type be for fields containing large amounts of data? Or is this just something that we have to live with in Access?

[aytee111]

Maybe someone else knows what the solution is for memo fields. I have not found a way to intercept the importing and tell Access that it is a memo field. You can do it when importing manually, you could also add a "dummy" row as row 1 in the Excel file that has lots of data in the memo column (dates in the date columns, numbers in the number columns, etc). You could even do that in VBA prior to importing the file.