ADO connection to Excel

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Are you using IMEX=1 in the connection string?
 
Upvote 0
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"";"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
To the best of my recall the valid values for that key are 0-16 only.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top