Any way to force Access to use data type for linked tables?

[ggranger007]

I am having issues with an earlier join created against a linked table. Access decided it wanted to change from "text" to the "number" data type and now I am receiving a "type mismatch in expression" error.

The issue is, most of the data in the linked table is a number, however there are a few exceptions where it has to be checked against data with characters (i.e. 98A2). Is there any way to change or force the data type for a linked table?

 

[prashantvb]

Got stuck with the same trouble today, worked around by changing the discrepant Excel column format to 'Text' and saving the excel file (i needed the column to be text instead of access guessing it to be number) ... revert the column back to 'General' cell formatting and save the excel file just before exiting the VBA routine.

This might be a preferable method than risking unwanted data rows in base excel file (just in case the ) and also having to handle these extra rows by additional coding.

Thank you Xenou - your remark on spreadsheet data typing prompted me to try out this method.

 

[koltregaskes]

Does anyone on this thread have the VBA script they used to clean up the data?