Link Excel file to Access, wrong data type

Topic

New Reply

I have some code that links an Excel spreadsheet to an Access database then does some work with the data in that linked table.

One of the fields has traditionally held numeric values, but recently we’ve been receiving some that have text values (A123456789 instead of 1234567890) and the link fails to recognize it, and the column is created as Numeric.

How do I fix it so I don’t lose my Text values?

Thanks!

Reply | Quote

Replies

Are you actually changing the data in the Excel workbook, or are you doing things in a temporary table or query. If you are actually editing the Excel data, that becomes a challenge. The linking process that decides what sort of data you are dealing with and sets the field values looks at somewhere between 10 and 100 rows – I’m sure it must be documented somewhere – and then says text or numeric. If on the other hand you are importing it into a temporary table, then you can set the data type using queries on linked tables, or you can use the TransferSpreadsheet with an import specification. If some of that doesn’t make sense, post back.

Reply | Quote

I have found that it checks the first 25 rows to determine the data type.
What i would do is import the excel sheet using TransferSpreadsheet as Wendell suggests into a new table and check the data types against the data types of the field that you are going to append to.

Reply | Quote

Another option is to open the spreadsheet in code prior to linking, and sort on the problem column, to force a text value to the top.

Reply | Quote

My code links the table then take each record and inserts it into other tables based on certain criteria.

I’ve tried formatting the Excel sheet column to text and I’ve tried putting the non-numeric values at the top, but to no avail.

This is the code I use:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, “blahblah”, , True, “”

It’s right after this piece that I can check the linked table and see that the column has the wrong data type.

Sign me Very Frustrated!

Reply | Quote

The issue is that you are using the acLink option. To really solve this kind of problem, you will have to create a table with field names that match the columns in the Excel worksheet (or specify that there are no field names) and use the acImport option. Then do your manipulations using the table you imported the worksheet (or range) to. I was incorrect in indicating that there was a import specification you could use with TransferSpreadsheet. That can only be used with TransferText – which is an option if you are willing to save your Excel workbook as a .csv file (which I doubt you would be).

Reply | Quote