What is the equivalent of an ODBCdirect workspace in Access 2010?

[dsatino]

I have numerous applications built in Access 2000/2003 that all use ODBCdirect workspaces to access various non-Access databases. Unfortunately, ODBCdirect is 'no longer supported'in Access 2010 and when my associates get new PC's, the applications fail.

The error message says to switch from DAO to ADO. This is a little curious since ADO is dying/dead as well.

I'm hoping someone can add a little insight/best practice to my situation.

[PianoMan64]

dsatino,

Since ODBCDirect was deprecated from MS Access 2007, and there were a lot of articles on that deprecation, the use of DAO (for MS Access Databases) and ADO (for everything else) is the preferred method, until MS Access starts using .NET. Currently right now, ADO is the most flexible to use and you can use it on anything that you want to connect too provided you have OLEDB drivers installed for that database type that you're trying to connect too.

Currently right now, Microsoft provides OLEDB connecters for MS SQL, MS Access, and Oracle. You can reference those by the DLL's that are installed for OLEDB. You can google on how to access OLEDB.

Since I use Oracle as My backend, the provider library is MSDAORA.

You would connect with something like this:

Dim PresCON as ADODB.Connection
Set PresCON = new ADODB.Connection
With PresCON
     .Provider = "MSDAORA"
     'use XE this is you're using the express edition, 
     'or whatever name it is if using full version.
     .Properties("Data Source").Value = "XE" 
     .Properties("User ID").Value = "UserNameHere"
     .Properties("Password").Value = "PasswordHere"
End with