I am getting a “Type Conversion” error when I import an Excel file into my Access Database. In this Database, “EmployeeID” is defined as text length 6. In our production General Ledger system, EmployeeID is also an alphanumeric field length 6. Most EmployeeIDs are 6 numbers (e.g. 875327 or 005871), but some EmployeeIDs are 5 numbers followed by one letter (e.g. 85419J). When we download production data into an Excel file, it seems that the EmployeeIDs that are all digits appear in Excel as either numbers or numbers formatted as text. The EmployeeIDs that contain a letter appear to be text on the Excel file. When I try to import an Excel file containing both types of EmployeeIDs, the fields that contain a letter are not imported due to this type conversion error. Why would this happen if my Access DB field is defined as text? How do I avoid this problem? I would highly appreciate any help. Thank you.

Access reads the first about 12 or 20 lines in the excel sheet to decide what datatype it will use for each column. If in this rows no alphanumerical, only numerical values are contained then Access will use a numeric datatype that will cause this type conversion error in later records.
To prevent this (in A2007 you will be able to specify the datatype by an import specification) you can do following:
- Add an additional line after the headerline that contains an alphanumerical value in this field for example '$$$$$$'
- import the Excel sheet into your database
- delete the added record
(DELETE FROM YOUR TABLE WHERE EmpID = '$$$$$$')

HTH
Henry