New Data Source for Linked Table in DB

[AlexB123]

Hello all,

I've been working to transition an access database to a new datasource. Previously, the inputs were copied and pasted from a query tool window in to an excel macro workbook, where the Unique ID's were treated as a "General" number, and the macro compiled all the results and saved them to a link file (linked table).

Now the inputs come as individual spreadsheets and the Unique ID's are treated as text. When the macro runs and compiles the data, my linked table now has a different (a text string) data type for my Unique ID's. The queries that try to join my main table, tblMain, to this linked table, tblSQLLink, now return: "Run-time error '3615': Type mismatch in expression".

How should I proceed? Use a query to rewrite the data types of my underlying Unique ID's in tblMain? Or use the Excel Macro Workbook to do some type of data conversion before saving the data to my linkfile? Which approach is the least likely to cause future bugs?

Below is code for one of my "import process" queries. Thanks for any suggestions!

SELECT tblMain.StatusID, tblMain.FirstName, tblMain.LastName, tblMain.Address1, tblMain.Error
FROM tblSQLLink INNER JOIN tblMain ON tblSQLLink.DBID = tblMain.ID
WHERE (((tblMain.StatusID)=1));

 

[xenou]

Hi,
I think if the ids are numbers (1, 2, 3 ...) it would probably makes sense to just clean up the data in the Excel workbook to make sure they are stored as numeric values in Excel.

If the ids aren't all numbers (1, 2, 3, A, 4, 5, B, ...) then you have to do the same thing (make sure all the values are stored as text in Excel. And you have to change the data type in Access).

In short, because Excel is the "free form" storage container than can have numbers and text in the same column, you have to make sure Excel is consistent and has the same data type as Access.