OpenDatabase Method

Applies To

Workspace Object.

Description

Opens a specified database in a session and returns a reference to the Database object that represents it. The open database is automatically added to the Databases collection.

Syntax

Set database = [workspace.]OpenDatabase(dbname[, exclusive[, read-only[, úsource]]])

The OpenDatabase method syntax has these parts.

Part	Description

database	A variable of a Database object data type that references the Database object that you're opening.
workspace	A variable of a Workspace object data type that references the existing Workspace object that will contain the database.
dbname	A string expression that is the name of an existing database file.
	Some considerations apply when using dbname:
	u If it refers to a database that is already open for exclusive access by another user, an error occurs.
	u If it doesn't refer to an existing database or valid ODBC data source name, an error occurs. (See Remarks for more information on opening ODBC databases.)
	u If it's a zero-length string ("") and source is "ODBC;", a dialog box listing all registered ODBC data source names is displayed so the user can select a database. (See Remarks for more information on opening ODBC databases.)
exclusive	A Boolean expression that is True if the database is to be opened for exclusive (nonshared) access and False if the database is to be opened for shared access. If you omit this argument, the database is opened for shared access.
read-only	A Boolean value that is True if the database is to be opened for read-only access and False if the database is to be opened for read/write access. If you omit this argument, the database is opened for read/write access.
source	A string expression used for opening the database, including databases with passwords. Note that the NewPassword method, when used on a Database object, changes the password parameter that appears in the "PWD=..." part of this argument. You must supply the exclusive and read-only arguments to supply a source string. See the Connect property for syntax, and see Remarks for more information on opening ODBC databases.

Remarks

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

Because attached tables on ODBC databases improve performance, it's preferable to use them rather than opening ODBC databases directly. To open an ODBC database directly, you must specify a registered ODBC data source name. If the filename has an extension, it's required. If your network supports it, you can also specify a network path, such as:

"\\MYSERVER\MYSHARE\MYDIR\MYDB.mdb"

For more information about ODBC drivers, such as SQL Server, see the Help file provided with the driver.

Note

The source argument is expressed in two parts: the database type, followed by a semicolon (;) and the optional arguments. The database type, such as "ODBC;" or "FoxPro 2.5" must be provided first. 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, .source = ("FoxPro 2.5; pwd=mypassword"). See the source argument, defined earlier in this topic, for more information.

See Also

Close Method, Connect Property.

Specifics (Microsoft Access)

In Microsoft Access, use the CurrentDb function to point a Database variable to the current database, rather than using the OpenDatabase method. For example, you can use the following code to get a variable that represents the current database.

Dim dbsCurrent as Database
set dbsCurrent = CurrentDb

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

Example

This example opens the Employees table in the database in the default Workspace object. If the database can't be opened (for example, if it has already been opened for exclusive access by another user in a multiuser environment), an error occurs and a message is displayed. Then the example updates the Title field of the Employees table.

Function UpdateNorthwind () As Integer
    Dim wspDefault As Workspace, dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim intErrorCondition As Integer, intUpdateNorthwind As Integer
    intUpdateNorthwind = True
    intErrorCondition = False
    On Error GoTo DBErrorHandler    ' Enable error trapping.
    Set wspDefault = DBEngine.Workspaces(0)

    ' Open database.
    Set dbsNorthwind = wspDefault.OpenDatabase("Northwind.mdb")
    If intErrorCondition = False Then
        On Error GoTo TableErrorHandler    ' Enable error trapping.
        ' Open table.
        Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
        If intErrorCondition = False Then
            On Error GoTo EditErrorHandler    ' Enable error trapping.
            Do Until rstEmployees.EOF
                If rstEmployees![Title] = "Sales Representative" Then
                    rstEmployees.Edit    ' Enable editing.
                    rstEmployees![Title] = "Account Executive"   
                    rstEmployees.Update    ' Save changes.
                End If
                rstEmployees.MoveNext    ' Move to next record.
            Loop
        End If
        dbsNorthwind.Close    ' Close database.
    End If
    On Error GoTo 0        ' Disable error trapping.
    Exit Function
DBErrorHandler:
    intErrorCondition = True
    intUpdateNorthwind = False
    MsgBox "Can't open database.", vbExclamation
    Resume Next
TableErrorHandler:
    intErrorCondition = True
    intUpdateNorthwind = False
    MsgBox "Can't open Employees table.", vbExclamation
    Resume Next
EditErrorHandler:
    intErrorCondition = True
    UpdateDb = False
    MsgBox "Can't edit Employees table.", vbExclamation
    Resume Next
End Function

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, using the OpenDatabase method. The procedure then enumerates all TableDef objects in both databases.

Sub OpenAnother()
    Dim dbs as Database, dbsAnother as Database
    Dim tdf as TableDef
    
    ' Return Database variable representing current database.
    Set dbs = CurrentDb

    ' Return Database variable representing Another.mdb.
    Set dbsAnother = DBEngine.Workspaces(0).OpenDatabase("Another.mdb")
    ' Enumerate all TableDef objects in each database.
    For Each tdf in dbs.TableDefs
        Debug.Print tdf.Name
    Next tdf
    For Each tdf in dbsAnother.TableDefs
        Debug.Print tdf.Name
    Next tdf
End Sub

Example (Microsoft Excel)

This example displays a custom dialog box containing a list of all databases with the filename 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 directory"
    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