Any Way to Force Default Connection to SQLOLEDB Driver?

[Will]

After upgrading machines to Windows 2003 SP1, we are noticing strange
behavior of recordsets in Access 2003. There are many similar reports on
the Internet, and it appears that these trace to the fact that the default
connection in Access is not using the SQLOLEDB driver. Apparently the ODBC
driver is not very stable, and the SQLOLEDB driver is what we want to be
using. Is there a way to force Access to use SQLOLEDB for its *default*
connection, or do we just have to use our own custom connection if we want
to control driver selection?

My programmer had the concern that even if we could force the default
connection to use OLEDB that this might cause the bound forms to start
behaving unpredictably.

Any advice on how to get Access using SQLOLEDB across the board is
appreciated.

 

[Rick Brandt]

After upgrading machines to Windows 2003 SP1, we are noticing strange
behavior of recordsets in Access 2003. There are many similar
reports on the Internet, and it appears that these trace to the fact
that the default connection in Access is not using the SQLOLEDB
driver. Apparently the ODBC driver is not very stable, and the
SQLOLEDB driver is what we want to be using. Is there a way to
force Access to use SQLOLEDB for its *default* connection, or do we
just have to use our own custom connection if we want to control
driver selection?

My programmer had the concern that even if we could force the default
connection to use OLEDB that this might cause the bound forms to start
behaving unpredictably.

Any advice on how to get Access using SQLOLEDB across the board is
appreciated.

You can't. And there is nothing "unstable" about the ODBC driver.

 

[Norman Yuan]

If your are talking Access front end connecting to SQL Server, you have two
ways: with *.mdb, you connect via ODBC, no other option; with *.adp, you use
SQL OLEDB, no other option, agian.

 

[Will]

If your are talking Access front end connecting to SQL Server, you have two
ways: with *.mdb, you connect via ODBC, no other option; with *.adp, you use
SQL OLEDB, no other option, agian.

Using ADP on Windows 2000, the debugger is reporting that the Access data
provider is different for Access 2000 than for Access 2003. For Access
2000 it is MSDataShape. For Access 2003 it is Microsoft.Access.OLEDB.10.0.
When we create a custom connection, we see the provider directly as OLEDB.

The behavior of recordsets is different with all of these providers.
We did experiments, and when our code uses default connections and ADP /
OLEDB using the above providers, it is breaking. When we create a custom
connection and use OLEDB directly, then the application is well behaved on
both Access 2000 and Access 2003.

So I was trying to find a way to affect which of the providers is used for
the default connection, hoping to find a way to make it OLEDB rather than
these strange higher level ADO constructs that are layered on top of OLEDB
and that seem to not work very reliably for us.

--
Will