Provider independent data access: problem with Excel ODBC source

[Guest]

VS2005
I have an application that is used by a variety of users. The application
analyzes some data and writes the results into a database.

Different users use different databases, and sometimes the same user will
use a different database at different times.

So, I want the user to be able to specifiy the database to be used at
runtime using a UDL file (or something that is easy for the user to work
with).

To get the basic user interface configured, I began with a VFP version of
the database. The user interface involves a grid that is populated by one
table from the database.And a listbox populated by another table in the
database.

I am assuming Ole Db Providers. For VFP: Ole Db Provider for Visual FoxPro.
For Excel, Ole Db Provider for ODBC Drivers.

At runtime, I look at the connection string that the user has specified, and
use the DbProviderFactory capability in System.Data.Common to create
appropriate table adapter, connection, and command objects. All of this works
fine when filling the tables from the VFP database.

However, when I attempt to fill the tables from the Excel version of the
database I get an ODBC error containing the following message:

ERROR [IM002][Microsoft][ODBC Driver Manager]Data source name not found and
no default driver specified.â€

In setting up the Excel workbook as the data source, I first used the ODBC
Administrator to make it an Excel ODBC data source, and then the Data Link
wizard to create an OLE DB Provider for ODBC Drivers connection string.

I would appreciate some guidance on how to proceed. I suspect that the Ole
DB Provider for ODBC Drivers is my problem.

 

[Paul Clement]

¤ VS2005
¤ I have an application that is used by a variety of users. The application
¤ analyzes some data and writes the results into a database.
¤
¤ Different users use different databases, and sometimes the same user will
¤ use a different database at different times.
¤
¤ So, I want the user to be able to specifiy the database to be used at
¤ runtime using a UDL file (or something that is easy for the user to work
¤ with).
¤
¤ To get the basic user interface configured, I began with a VFP version of
¤ the database. The user interface involves a grid that is populated by one
¤ table from the database.And a listbox populated by another table in the
¤ database.
¤
¤ I am assuming Ole Db Providers. For VFP: Ole Db Provider for Visual FoxPro.
¤ For Excel, Ole Db Provider for ODBC Drivers.
¤
¤ At runtime, I look at the connection string that the user has specified, and
¤ use the DbProviderFactory capability in System.Data.Common to create
¤ appropriate table adapter, connection, and command objects. All of this works
¤ fine when filling the tables from the VFP database.
¤
¤ However, when I attempt to fill the tables from the Excel version of the
¤ database I get an ODBC error containing the following message:
¤
¤ ERROR [IM002][Microsoft][ODBC Driver Manager]Data source name not found and
¤ no default driver specified.”
¤
¤ In setting up the Excel workbook as the data source, I first used the ODBC
¤ Administrator to make it an Excel ODBC data source, and then the Data Link
¤ wizard to create an OLE DB Provider for ODBC Drivers connection string.
¤
¤ I would appreciate some guidance on how to proceed. I suspect that the Ole
¤ DB Provider for ODBC Drivers is my problem.

My understanding is that the OLE DB Provider for ODBC (MSDASQL) is not supported by ADO.NET.

INFO: OLE DB .NET Managed Provider Does Not Support Some OLE DB Providers in Visual Basic .NET
http://support.microsoft.com/kb/310448


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Jim,

Maybe it is well if you take this to your knowledge,

Cor

 

[Cor Ligthert [MVP]]

http://blogs.msdn.com/adonet/archive/2007/03/16/ado-net-orcas-sample-provider.aspx

Sorry I cannot find it anymore but it is in 2.0 as well.

Cor

Jim,

Maybe it is well if you take this to your knowledge,

Cor

VS2005
I have an application that is used by a variety of users. The application
analyzes some data and writes the results into a database.

Different users use different databases, and sometimes the same user will
use a different database at different times.

So, I want the user to be able to specifiy the database to be used at
runtime using a UDL file (or something that is easy for the user to work
with).

To get the basic user interface configured, I began with a VFP version of
the database. The user interface involves a grid that is populated by one
table from the database.And a listbox populated by another table in the
database.

I am assuming Ole Db Providers. For VFP: Ole Db Provider for Visual
FoxPro.
For Excel, Ole Db Provider for ODBC Drivers.

At runtime, I look at the connection string that the user has specified,
and
use the DbProviderFactory capability in System.Data.Common to create
appropriate table adapter, connection, and command objects. All of this
works
fine when filling the tables from the VFP database.

However, when I attempt to fill the tables from the Excel version of the
database I get an ODBC error containing the following message:

ERROR [IM002][Microsoft][ODBC Driver Manager]Data source name not found
and
no default driver specified."

In setting up the Excel workbook as the data source, I first used the
ODBC
Administrator to make it an Excel ODBC data source, and then the Data
Link
wizard to create an OLE DB Provider for ODBC Drivers connection string.

I would appreciate some guidance on how to proceed. I suspect that the
Ole
DB Provider for ODBC Drivers is my problem.