OpenDatabase Method

Applies To DBEngine object, Workspace object.

Description

Opens a specified database in a Workspace object and returns a reference to the Database object that represents it.

Syntax

Set database = workspace.OpenDatabase(dbname, options, read-only, connect)

The OpenDatabase method syntax has these parts.

Part	Description

database	An object variable that represents the Database object that you want to open.
workspace	Optional. An object variable that represents the existing Workspace object that will contain the database. If you don't include a value for workspace, OpenDatabase uses the default workspace.
dbname	A String that is the name of an existing Microsoft Jet database file, or the data source name (DSN) of an ODBC data source. See the Name property for more information about setting this value.
options	Optional. A Variant that sets various options for the database, as specified in Settings.
read-only	Optional. A Variant (Boolean subtype) value that is True if you want to open the database with read-only access, or False (default) if you want to open the database with read/write access.
connect	Optional. A Variant (String subtype) that specifies various connection information, including passwords.

Settings

For Microsoft Jet workspaces, you can use the following values for the options argument.

Setting	Description

True	Opens the database in exclusive mode.
False	(Default) Opens the database in shared mode.

For ODBCDirect workspaces, the options argument determines if and when to prompt the user to establish the connection. You can use one of the following constants.

Constant	Description

dbDriverNoPrompt	The ODBC Driver Manager uses the connection string provided in dbname and connect. If you don't provide sufficient information, a run-time error occurs.
dbDriverPrompt	The ODBC Driver Manager displays the ODBC Data Sources dialog box, which displays any relevant information supplied in dbname or connect. The connection string is made up of the DSN that the user selects via the dialog boxes, or, if the user doesn't specify a DSN, the default DSN is used.

(continued)

dbDriverComplete	(Default) If the connect and dbname arguments include all the necessary information to complete a connection, the ODBC Driver Manager uses the string in connect. Otherwise it behaves as it does when you specify dbDriverPrompt.
dbDriverCompleteRequired	This option behaves like dbDriverComplete except the ODBC driver disables the prompts for any information not required to complete the connection.

Remarks When you open a database, it is automatically added to the Databases collection. Further, in an ODBCDirect workspace, the Connection object corresponding to the new Database object is also created and appended to the Connections collection of the same Workspace object.

Some considerations apply when you use dbname:

If it refers to a database that is already open for exclusive access by another user, an error occurs.
If it doesn't refer to an existing database or valid ODBC data source name, an error occurs.
If it's a zero-length string (" ") and connect is "ODBC;", a dialog box listing all registered ODBC data source names is displayed so the user can select a database.
If you're opening a database through an ODBCDirect workspace and you provide the DSN in connect, you can set dbname to a string of your choice that you can use to reference this database in subsequent code.

The connect argument is expressed in two parts: the database type, followed by a semicolon (;) and the optional arguments. You must first provide the database type, such as "ODBC;" or "FoxPro 2.5;". The optional arguments follow in no particular order, separated by semicolons. One of the parameters may be the password (if one is assigned). For example:

"FoxPro 2.5; pwd=mypassword"

Using the NewPassword method on a Database object other than an ODBCDirect database changes the password parameter that appears in the ";pwd=..." part of this argument. You must supply the options and read-only arguments to supply a source string. See the Connect property for syntax.

To close a database, and thus remove the Database object from the Databases collection, use the Close method on the object.

Note When you access a Microsoft Jet-connected ODBC data source, you can improve your application's performance by opening a Database object connected to the ODBC data source, rather than by linking individual TableDef objects to specific tables in the ODBC data source.

See Also Close method, Connect property.

Specifics (Microsoft Access)

In Microsoft Access, use the CurrentDb function to return a reference to the current database and assign it to a Database variable, as shown in the following example.

Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb

Use the OpenDatabase method to open a database other than the current database from Visual Basic.

Example

This example uses the OpenDatabase method to open one Microsoft Jet database and two Microsoft Jet-connected ODBC databases.

Sub OpenDatabaseX()

    Dim wrkJet As Workspace
    Dim dbsNorthwind As Database
    Dim dbsPubs As Database
    Dim dbsPubs2 As Database
    Dim dbsLoop As Database
    Dim prpLoop As Property

    ' Create Microsoft Jet Workspace object.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

    ' Open Database object from saved Microsoft Jet database
    ' for exclusive use.
    MsgBox "Opening Northwind..."
    Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", True)

    ' Open read-only Database object based on information in
    ' the connect string.
    MsgBox "Opening pubs..."
    Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
        dbDriverNoPrompt, True, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    ' Open read-only Database object by entering only the
    ' missing information in the ODBC Driver Manager dialog
    ' box.
    MsgBox "Opening second copy of pubs..."
    Set dbsPubs2 = wrkJet.OpenDatabase("Publishers", _
        dbDriverCompleteRequired, True, _
        "ODBC;DATABASE=pubs;DSN=Publishers;")

    ' Enumerate the Databases collection.
    For Each dbsLoop In wrkJet.Databases
        Debug.Print "Database properties for " & _
            dbsLoop.Name & ":"

        On Error Resume Next
        ' Enumerate the Properties collection of each Database
        ' object.
        For Each prpLoop In dbsLoop.Properties
            If prpLoop.Name = "Connection" Then
                ' Property actually returns a Connection object.
                Debug.Print "    Connection[.Name] = " & _
                    dbsLoop.Connection.Name
            Else
                Debug.Print "    " & prpLoop.Name & " = " & _
                    prpLoop
            End If
        Next prpLoop
        On Error GoTo 0

    Next dbsLoop

    dbsNorthwind.Close
    dbsPubs.Close
    dbsPubs2.Close
    wrkJet.Close

End Sub

Example (Microsoft Access)

The following example returns a Database variable that points to the current database. Then it opens a different database called Another.mdb by using the OpenDatabase method. The procedure then enumerates all TableDef objects in both databases.

To try this example, create a new database called Another.mdb, close it, and place it in the same directory as the database from which you are running the code.

Sub OpenAnother()
    Dim wsp As Workspace
    Dim dbs As Database, dbsAnother As Database
    Dim tdf As TableDef
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)
    ' Return reference to Another.mdb.
    Set dbsAnother = wsp.OpenDatabase("Another.mdb")
    ' Enumerate all TableDef objects in each database.
    Debug.Print dbs.Name & ":"
    For Each tdf in dbs.TableDefs
        Debug.Print tdf.Name
    Next tdf
    Debug.Print
    Debug.Print dbsAnother.Name & ":"
    For Each tdf in dbsAnother.TableDefs
        Debug.Print tdf.Name
    Next tdf
    Set dbs = Nothing
    Set dbsAnother = Nothing
End Sub

Example (Microsoft Excel)

This example displays a custom dialog box that contains a list of all the databases with the file name extension .mdb that are located in the Microsoft Excel folder, and then it opens the database selected by the user.

Dim a(100), db As Database
i = 0
ChDrive "C"
ChDir Application.Path
a(i) = Dir("*.MDB")
If a(i) = "" Then
    MsgBox "You have no databases in the Microsoft Excel folder"
    Exit Sub
End If
Do
    i = i + 1
    a(i) = Dir()
Loop Until a(i) = ""
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
For counter = 0 To i - 1
    list1.AddItem a(counter)
Next
Application.ScreenUpdating = True
theDialog.Show
Set db = Workspaces(0).OpenDatabase(a(list1.Value - 1))
MsgBox "The " & db.Name & " database is now open"
' use database here
db.Close