How does Access via ADODB treat different Excel formats?

**dmasters4919** · 01-29-2015, 10:59 AM

If I open up a connection to Access with ADODB from an Excel file, the formats seem to become somewhat inconsistent.

For example, I have a column in Excel that contains integers, with some formatted as text and others formatted as numbers. However, if I do a simple "Select *" query using Access and then copy the recordset back into Excel, the ENTIRE column will have been changed to either text OR numbers. The strangest part is that I can't find any reason why it goes to one or the other. If the first 4 out of 15 values are formatted as text and the rest are numbers, it would convert all values to text. However, if the first 3 are formatted as text, the 4th is a number, the 5th is text, and the rest are numbers, it will convert all 15 values to numbers. This seems really incosistent to me and I have no idea what its basing the conversion off of.

I can fix this by using an IIF and CStr function but I would really like to know if anyone has a clue about this first.

Thanks!

**AB33** · 01-29-2015, 11:10 AM

Excel is "Free for all" package which means you can do dump up any form of data to anywhere. Databases, however, have some constraints and restriction on the type of data you can include as value. If a column type is an integer, all values on that column should be integers.