You can also connect to an ODBC data source by using the OpenDatabase method to open a Database object. However, the Database object in an ODBCDirect workspace doesn’t support all of the functionality of a Connection object. Specifically, if you’re using a Database object, you can’t connect asynchronously, run queries asynchronously, or define QueryDef objects that represent queries in the ODBC data source.
To connect to an ODBC data source with the OpenDatabase method in an ODBCDirect workspace, specify a valid connection string for the connect argument of the OpenDatabase method, as shown in the following example.
Dim wrk As Workspace, dbs As Database
Dim strConnect As String
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "Admin", "", dbUseODBC)
Set dbs = wrk.OpenDatabase("Pubs", dbDriverNoPrompt, False, strConnect)