Change Filed type using VB

[JohnR]

I am importing an Excel spreadsheet into Access. Access in its infinite
wisdom decides on the data type for the fields in the import. I need to be
able to use VBA code to change the field type to what is needed for my
queries. Does anyone have some sample code for changing the data atype of
fields?

Thank you in advance

John

 

[Jeff Boyce]

John

An alternative to just "taking it" and changing it via VBA code would be to
create a table definition in Access that has the field data types you
need/want.

Then link to (or import) the raw Excel data.

Then create one/more parsing queries that take the data from the raw/import
and convert it (as/if needed), and append it into your permanent table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnR]

I have used that method in the past with success. However, in this case the
number of columns/fields in the Excel spreadsheet changes. I need to be
able to import any sheet. The user then indicates the key field, and the
files is exported with a few extra fields added based on the key field.
Unfortunately the key field comes in soemtimes as test and soemtimes as a
number. I need to be able to change it.

 

[Jeff Boyce]

John

"numbers" (1, 27.65, -44) are text, too. Import them as is, use a query to
parse them into a text field, then use the (if I recall) IsNumeric() and/or
Value() function to derive what you need to know.

Regards

Jeff Boyce
Microsoft Office/Access MVP