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 OpenDatabase method on the DBEngine object. Although the connection to the external database is temporary, the new Database object is still added to the Databases collection.

The OpenDatabase method is fairly straightforward.

The following table describes the OpenDatabase method arguments.

The following code demonstrates how to open several different databases using various techniques. After opening each database, you'll notice that the code prints the name of the database, and a count of the respective Databases collection.

Specifically, it opens the following databases from the following sources:

• Microsoft Access database
• dBase IV database using Jet
• SQL Server database using ODBC through Jet

Public Sub OpenSeveralDatabases(strUsrName As String, strPwd As String)
    Dim wsAccess As Workspace
    Dim dbAccess As DAO.Database
    Dim dbdBase As DAO.Database
    Dim dbODBC As DAO.Database

    'Create the Access workspace
    Set wsAccess = DBEngine(0)

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

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

    'Open a dBase IV database -exclusive -read-write
    Set dbdBase = wsAccess.OpenDatabase( _
	"dBase IV;DATABASE=C:\Temp\db2.dbf", True, False)
    Debug.Print "Database "; wsAccess.Databases.Count & _
	"-"& dbdBase.Name

    'Open an ODBC database using a DSN -exclusive -read-only
    Set dbODBC = wsAccess.OpenDatabase( _
	"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN")
    Debug.Print "Access Database "; wsAccess.Databases.Count & _
	"-"& dbODBC.Name

    'Clean up
    wsAccess.Close
    Set dbAccess = Nothing
    Set dbdBase = Nothing
    Set dbODBC = Nothing
    Set cn = Nothing
    Set wsAccess = Nothing
End Sub