ADO Query of Excel Table is returning with incorrect Field Type

[brusk]

I've been playing with ADO recently to query a bunch of ListObject tables I have in Excel. It seems to work pretty good for all my queries and updates until I got to one table. It's kinda of my dumb all table for settings and isn't a standard format like most that have either all numeric, text or something in it. This table only has 3 fields which the last has a combination of text, numberic, boolean etc. I've tried resaving all the data in as text and converted the cell formats to text but for some reason it still assigns that last field as an adDouble. Is there any tricks to tell it what the format is.

Thank,
Bruce

[p45cal]

Maybe, just maybe: A quote from the following site: https://groups.google.com/forum/#!to...do/8P93mbel-GY
"The Excel ISAM driver makes its decision on the row's datatype based on a
sampling, usually of the first 8 rows. If the datatypes are mixed, it
chooses the "majority" type, leaning in favor of numbers (adDouble) if
there's a tie. Values of the "minority" type are returned as NULLs, so even
once we figure out this datatype issue, you're not necessarily out of the
woods yet... there are unfortunately a number of quirks working with Excel
and ADO."

There are some knwledge base articles below this quote which might be of use to you.

[brusk]

I found a few posts that offered a registry key for some version but unfortunately whatever I get working has to work out of the box for others in my company. The sampling is a hard one as I'm having it contantly overwrite in weird orders on that table so sometimes alot of numbers are toward the top and sometimes text. I could try throwing in half a dozen text records to see if it changes.

[westconn1]

as you have not shown your connection string

try IMEX = 1, but i believe it will only work with readonly recordsets

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;hdr=yes;IMEX=1;"""
    .Open
End With

change as required

[brusk]

The current code that I got from another use is the following:

ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & SourceWorkbookPath & ";" & _
    "Extended Properties='Excel 8.0;HDR=" & IIf(TablesHaveHeaders, "Yes", "No") & "'"
    RecordSet.Source = SQLCommand
    RecordSet.CursorType = adOpenKeyset
    RecordSet.LockType = adLockOptimistic
    RecordSet.Open ActiveConnection:=ConnectionString 'SQLCommand, ConnectionString ', 3, 3, 1 ' adOpenDynamic, adLockReadOnly, adCmdText

I think it definately needs tweaked as I'm also getting inconsistant errors about read only tables. At this point I need to get a better understanding of this or revert back to not even using ADO.

[brusk]

I tried changing the connection string with the IMEX. Get a different error now, that might be a good thing. Running 64Bit Windows 7.

    ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;hdr=yes;IMEX=1;"
    RecordSet.Open SQLCommand, ConnectionString, adOpenDynamic, adLockReadOnly, adCmdText

Run-time error '3706'. Provider cannot be found. It may not be properly installed.

[brusk]

Quite a few typos in the previous one, forgot the ; after the Provider and missed the single quotes for the Extended Properties section. The plus part I'm starting to get alot of experience researching ADO stuff after the last 12 different errors I recieved.

Here's the final part of what is currently working. BTW I was not able to do the .provider = layout as it always complained it wasn't a valid parameter.

    ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties='Excel 8.0;hdr=yes;IMEX=1'"
    RecordSet.Open SQLCommand, ConnectionString, adOpenDynamic, adLockReadOnly, adCmdText

[westconn1]

if it is all working now, pls mark thread resolved

or revert back to not even using ADO.

there are restrictions using ADO with excel workbooks, but hard to beat, when returning queries on large sheets, or updating master sheet with unique records