Linked Excel file column default to numeric where it should be string how to force to string?

Question

I have a linked excel file in Access.  When I open the file, I can see that the project ID column is numeric as the first few rows are numbers but subsequent rows are alphanumeric.  And those subsequent alphanumeric rows will appear as #Num!.

How do I force Access to recognise this column as string?

Because I use this file in a query and I got data type mismatch because I was expecting this column to be string.  I can't convert using CStr because those alphanumeric rows will be errors.

Although I overcame the problem by adding another column in Excel to convert this column into string, I wish there could be an easier way as I have to do this every week whenver I receive a new file (a new file is downloaded from another system and I have to use my Access programme to run certain queries).

Answer 1

I resolved this issue in Access using vba to call Excel, insert a schema row (after 1st row), if text, insert a string, if numeric insert a number, save it, then select via SQL, the columns will be interpretted accordingly.

Answer 2

Access looks at a couple of rows to decide what the datatype is.

Add a phony row in the Excel directly under the column names that have alpha characters in those that are text and re-link.

Answer 3

thanks for the valuable subject so thanks

While it is nice to thank people, you really shouldn't revive an old thread by doing so. It makes it appear as if there is new activity and can waste people's time reviewing the thread.

So please check the dates before reviving old threads as you have done several times.

Answer 4

One way is to name the linked Excel table something else and create a query with the original Excel name, using a calculated field so as to get text.

Answer 5

It does resolve the issue, but I was looking for an Access solution.  Your method is no difference from mine method of adding another column in excel to convert numeric to string, in both methods I have to do it every week with a new file.  I was looking for a "permanent" solution in using Access coding.