Excel as an OLE DB consumer

[darrenk]

Hi,
How can I best simulate Excel as an OLE DB consumer?

I would like to be able to write an app using ADO or ? to reproduce a
data retrieval problem I'm having with a custom OLE DB provider app.

The app behaves much like the MS SQL OLEDB provider.
Data access from ADO and the custom client works fine.

But data access from Excel is not working. The string data is not being
shown. I would like to be able to repro this by writing an OLE DB
consumer that exhibits the same behviour as Excel.

Thanks
-Darren

 

[Cindy M -WordMVP-]

How can I best simulate Excel as an OLE DB consumer?

I would like to be able to write an app using ADO or ? to reproduce a
data retrieval problem I'm having with a custom OLE DB provider app.

The app behaves much like the MS SQL OLEDB provider.
Data access from ADO and the custom client works fine.

But data access from Excel is not working. The string data is not being
shown. I would like to be able to repro this by writing an OLE DB
consumer that exhibits the same behviour as Excel.

The OLE DB provider used for Excel is actually that of the JET engine.
And it uses Excel's ODBC driver for the connection. The ODBC driver
performs a check on the first eight rows (by default, this can be
configured) of each column to determine the data type for the column. If
it decides it's numeric, strings aren't shown. If it decides it's a
string, numbers don't come through. So you need to explicitly type the
columns in the Excel spreadsheet (best through the Data/Text to columns
wizard).

If that's not the problem you're seeing, then please explain the problem
in more detail.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[keepITcool]

OP:

you probably need to look into the setting for IMEX
see http://www.dicks-blog.com/archives/2004/06/03/
used when Jet Engine accesses "mixed data" in excel files.


Cindy,
I think you're confused on data access technology

The OLEDB Provider for JET definitely does not use ODBC.

in fact ADO needs an intermediate OLEDB provider for ODBC to
communicate with the ODBC drivers, whereas it can directly use the
OLEDB provider for Jet.

Both ODBC and OLEDB for Jet end up using the "engine": msjet40.dll


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Cindy M -WordMVP- wrote :

 

[darrenk]

Some more details on this problem -

My custom OLEDB provider is returning table and column names as WSTR's.
These are displaying fine.

The same OLEDB procider is returning string row data as BSTR's. These
are not displaying and there are no errors.

The DBBINDINGs that Excel passes to CreateAccessor specify BSTR's.
I'm using IDataConvert:ataConvert to set the data for the consumer.
Numeric and date values show up fine. The BSTR's are not being
displayed. I've tried setting the Excel column type to text.

I'd like to try and simulate this behaviour with some client code, but
I don't know what Excel uses to connect to and query an OLE DB
provider.

Any advice is appreciated.

TIA
-Darren

 

[peregenem]

The OLE DB provider used for Excel is actually that of the JET engine.
And it uses Excel's ODBC driver for the connection.

Incorrect. odbc is not used.

you need to explicitly type the
columns in the Excel spreadsheet (best through the Data/Text to columns
wizard).

Incorrect. best done via SQL data definition language (ddl) or
equivalent (eg adox).

 

[Cindy M -WordMVP-]

Hi KeepITcool,

I think you're confused on data access technology

The OLEDB Provider for JET definitely does not use ODBC.

in fact ADO needs an intermediate OLEDB provider for ODBC to
communicate with the ODBC drivers, whereas it can directly use the
OLEDB provider for Jet.

Both ODBC and OLEDB for Jet end up using the "engine": msjet40.dll

I'm willing to believe you, but can you point me to some
documentation on this? I do seem to remember that OLE DB for JET
goes over the Excel ODBC provider to pull Excel data, although I
can't recall where I read that.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Cindy M -WordMVP-]

you need to explicitly type the

Incorrect. best done via SQL data definition language (ddl) or
equivalent (eg adox).

I stand corrected for the environment the OP is using. That won't work for
mail merge, though (where my mind was when I answered).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[keepITcool]

documentation?

http://msdn.microsoft.com/data/DataFundamentals/dataaccess/default.aspx


Following is an interesting page..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht
m/odbcjetdesktop_database_drivers_architecture.asp

And I grant you... msjet40.dll is described here as an
'ODBC Desktop Database driver'
whereas I consider it an engine. and so does microsoft:
file Properties: Microsoft Jet Engine Library

The OLEDB for JET provider communicates directly with the DLL
check dependencies on msjetoledb40.dll

ODBC goes thru many more layers.


sorry cutting and pasting these long urls is not working
and im too lazy to use tinyurl



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Cindy M -WordMVP- wrote :

 

[darrenk]

So can anyone say what Excel IS using to query an OLE DB provider?