CODEWITHSUNDEEP
vbams-access
A. Kru
A. Kru

Reputation: 37

VBA Access Transfertext Import Errors

I would like to import a spreadsheet to an access database, on column has ages 1-89 plus another that's says 90+ which in turn create a Import Error. Using the DoCmd.TransferText, is it possible to import everything as it is including 90+ in the column of all other numbers?

Upvotes: 1

Views: 615

Answers (1)

Kiran
Kiran

Reputation: 66

If you import data into a table that doesn't already exist, Access will create one for you. It automatically determines the data type of each column based on the first few rows that are imported. If your source data contains a mixture of data types in one column, then you may experience this error.

There are 2 solutions:

  1. Build the import table to be of the correct data type for your data (i.e. specify that the age column is Short Text). Then import to the pre-defined import table.
  2. Ensure that the CSV or Excel file stores each age as a string i.e. "20" instead of 20 (in Excel, format the cell as Text, so it is left aligned or start cell contents with an apostrophe '20 or use formulaic notation ="20").

You could do either of those things, but it would be better to do both of them if possible.

Upvotes: 1

Related Questions