Opening an External Database

Sometimes you need to work with data in another Access database, a dBase IV database, or Excel spreadsheet, but you don't want a permanent link. You can do so by opening a temporary connection to it with the Workspace object's OpenDatabase method.

Although this method belongs to the Workspace object, I am describing it in this section because the end result is that a new (albeit temporary) Database object is added to the Databases collection.

The OpenDatabase method is fairly straightforward.

Set dbs = wrk.OpenDatabase(dbname, options, read-only, connect)

The following table describes the OpenDatabase method arguments.




A string value that represents the full path and filename of the database you want to open.


An optional Boolean True (—1) or False (0) that indicates whether to open the database in exclusive (True) or shared mode (False).


An optional Boolean True (—1) or False (0) that indicates whether to open the database as read-only.


An optional Variant connection string that specifies how to prompt the user to establish a connection (ODBCDirect workspaces only).

DbDriverNoPrompt The ODBC Driver Manager uses the connection string provided in the dbname and connect arguments.

If you don't provide sufficient information, a runtime error occurs.


Argument Description


The ODBC Driver Manager displays the ODBC Data Sources dialog box, which displays relevant information supplied in dbname or connect.

The connection string is composed of the DSN selected by the user, or the default DSN if none is selected.


(Default) If the dbname and connect arguments include sufficient information to complete a connection, the ODBC Driver Manager uses the string in connect. Otherwise it behaves as it does when you specify dbDriverPrompt.


This option behaves like dbDriverComplete, except that the ODBC driver disables the prompts for any information not required to complete the connection.

The following example code demonstrates how to open several different databases using different techniques. Specifically, it opens the following databases from the following five sources:

□ Microsoft Jet database

□ dBase IV database using Jet

□ SQL Server database using ODBC through Jet

□ SQL Server database using ODBCDirect

□ A second instance of an SQL Server database, using an existing connection

After opening each database, you'll notice that the code prints the name of the database, and a count of the respective Databases collection. Take particular notice of the database names and collection counts for the ODBCDirect databases.

You can see that the database name is that of the connection—not the database. This is because ODBCDirect has established a connection to the DSN, rather than the database; so it is the DSN's name that is returned.

Public Sub OpenSeveralDatabases(strUsrName As String, strPwd As String) Dim wsJet As Workspace Dim wsODBC As Workspace Dim dbJet As Database Dim dbdBase As Database Dim dbODBC As Database Dim dbODBCDirect As Database Dim dbODBCDirect1 As Database Dim cn As Connection

'Create the Jet and ODBCDirect workspaces Set wsJet = DBEngine(O)

Set wsODBC = DBEngine.CreateWorkspace( _ "", strUsrName, strPwd, dbUseODBC)

'Print the details for the default database Debug.Print "Jet Database "; wsJet.Databases.Count & _ " - " & CurrentDb.Name

'Open a Microsoft Jet database - shared - read-only Set dbJet = wsJet.OpenDatabase("C:\Temp\db1.mdb", False, True) Debug.Print "Jet Database "; wsJet.Databases.Count & _ " - " & dbJet.Name

'Open a dBase IV database - exclusive - read-write Set dbdBase = wsJet.OpenDatabase( _

"dBase IV;DATABASE=C:\Temp\db2.dbf", True, False) Debug.Print "Database "; wsJet.Databases.Count & _ " - " & dbdBase.Name

'Open an ODBC database using a DSN - exclusive - read-only Set dbODBC = wsJet.OpenDatabase( _

"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN") Debug.Print "Jet Database "; wsJet.Databases.Count & _ " - " & dbODBC.Name

'Open an ODBCDirect Connection using a DSN - read-only Set cn = wsODBC.OpenConnection( _

"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN") 'Get a reference to the default ODBCDirect database Set dbODBCDirect = wsODBC.Databases(O)

'This could so be written as: Set dbODBCDirect = cn.Database Debug.Print "ODBCDirect Database "; wsODBC.Databases.Count & _ " - " & dbODBCDirect.Name

'Open a second database reference using the ODBCDirect connection Set dbODBCDirectl = wsODBC.OpenDatabase( _

"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN") Debug.Print "ODBCDirect Database "; wsODBC.Databases.Count & _ " - " & dbODBCDirect.Name

'Clean up cn.Close wsJet.Close wsODBC.Close Set dbJet = Nothing Set dbdBase = Nothing Set dbODBC = Nothing Set dbODBCDirect = Nothing Set dbODBCDirectl = Nothing Set cn = Nothing Set wsJet = Nothing Set wsODBC = Nothing End Sub

0 0

Post a comment