DoCmd.Transferspreadsheet - Data definition

[Rafi]

I am trying to get Dta from Excel into an Access table by linking the
spreadsheet in access, doing some data manipulation and appending records to
a table. unfortunately, one of the columns contains ZIP Code data which is
entered in the spreadsheet as 12345 or 123456789 or 12345-6789. Access
assigns a data taype to the column based on the first few instances in the
spreadsheet which in my case happens to be a numeric and consequently,
subsequent data that is in the format 12345-6789 results in an error. My
question is how do I force access to assign a text data type to this
particular column. Is there a way to use an import spec file on a linked
file?

When I import the spreadsheet rathger than link it, the data come through ok
however, I get import errors that I am at a loss to understand. Is there a
way to identify the errot type beyond the very simplistic entry in the import
error table

Thanks for all the help
Your help is much appreciated.

 

[Allen Browne]

Can you set up the table with a Text field for this column? Then
TransferText into this table, and it should be recognised as text rather
than number?

If that doesn't work, you could link the Excel spreadsheet so you can create
a query into it. In the query, use Str() around the field to force the data
type, e.g.:
Str([Zip])
Then turn it into an Append query (Append on Query menu), map this to the
target field, and it should import.