ADO connection to Excel

[AlexanderBB]

Yikes ! I've just found my ADO connection in Access is not getting all the data. Some fields are blank. After a bit of googling it looks like I might need a Schema.ini File.

Has anyone any knowledge of this? Or had a similar issue and fixed it. I have made a mockup demo of just the problem. It's the same in Access 97 and 2003.

I have added dummy text to Excels first 8 rows to 'lock' the datatype. The problen doesn't occur until line 58 when a column contains just a dash. Maybe a clue ?

Thanks, ABB

 

[RoryA]

Are you using IMEX=1 in the connection string?

 

[AlexanderBB]

Yes rorya

theName = "E:\Book1.xls"
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & theName & ";" & _
"extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

 

[AlexanderBB]

Just tried IMEX=0 and no difference to IMEX=1.

But If I add a string of a's in the first 9 cells of the column, the missing data is then present. I did have dummy text in some columns, but not all. Do you need it in all?

Is there any other way to know you're reliably reading in all the data?

Thanks, ABB

 

[RoryA]

ADO is not great with Excel and mixed data, unless you can guarantee that the first few rows of each column will be text. You might well be better off with a csv file and a schema.

 

[Kreszch68]

Hi AlexanderBB,

go to the registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

and change the value of the key

TypeGuessRows

from 8 to 20

This might fix your problem

PS If you'r Excel file isn't really big, you can set the value to 0, this will scan all data, but of course this will take a lot more time to finish.

 

[RoryA]

To the best of my recall the valid values for that key are 0-16 only.

 

[Kreszch68]

I think you're right. You can set the value to 20 however, but it will only scan the first 16 rows.
So, setting the key to 0 would be the best option to avoid problems, but there is a risk of performance issues.

 

[AlexanderBB]

Thanks for the replies. Seems a common problem.... weird though, as I use Excel to read the Access tables perfectly, it's only when Access reads the Excel data. The connection string in Excel doesn't mention IMEX.

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows - set to 19
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes - set to Text

My findings are IMEX 0 or 1 both fail (lose data) and the only fix I have is to add text in Excel, and strip it out in Access when populating he table. All the fields bar one are text, btw.

CSV is a thought,but I think some fields may have comas. Never seen a schema file. Must google... can you have other delimiters instead of commas?

 

[RoryA]

Yes, though typically a CSV will include in quotes any field that has commas in it, so it shouldn't be an issue.

Re the field types, Access provides this information as it is a database program but Excel doesn't (since they aren't really tables).