Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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 :-) |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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 : 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). |
#4
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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::DataConvert 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 Cindy M -WordMVP- wrote: 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 :-) |
#5
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
Cindy M -WordMVP- wrote: 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). |
#6
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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 :-) |
#7
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
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). 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 :-) |
#8
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
documentation? http://msdn.microsoft.com/data/DataF...s/default.aspx Following is an interesting page.. http://msdn.microsoft.com/library/de.../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 : 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 :-) |
#9
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Excel as an OLE DB consumer
So can anyone say what Excel IS using to query an OLE DB provider?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to know percapita consumer debt. Debt amount is 2121.1 bill. | Excel Discussion (Misc queries) |