Database Object

Description

A Database object represents an open database.

Remarks

You manipulate an open database using a Database object and its methods and properties. You can examine the collections in a Database object to learn about its tables, queries, and relationships. You can also use its collections to modify or create tables, queries, recordsets, and relationships. For example, you can:

Use the Execute method to run an action QueryDef object or pass an SQL string to an ODBC database.
Use the OpenRecordset method to create a new Recordset object directly from the Database object.
Use the Close method to close an open database.

You use the CreateDatabase method to create a new, persistent Database object and append it to the Databases collection, thereby saving it to disk. After you create a new database, you must create new tables to hold your data. You can also create new indexes and table relationships, attach existing external tables, or establish a protection scheme using permissions, groups, and users.

To open an existing Database object, use the OpenDatabase method, which appends the object to the Databases collection. The OpenDatabase method applies to the Workspace object. You can use the OpenDatabase method on the default Workspace(0) object, or on a different opened Workspace object.

When you use one of the transaction methods (BeginTrans, CommitTrans, or Rollback) on the Workspace object, these transactions apply to all databases opened on the Workspace from which the Database object was opened. If you want to use independent transactions, you must first open an additional Workspace object, and then open another Database object in that Workspace object. For example, the following creates two independent Database objects:

Dim dbsThisOne As Database, dbsThatOne As Database
Dim wspFirst As Workspace, wspSecond As Workspace
Dim strUserName As String
Set wspFirst = Workspaces(0)    ' Use the default Workspace.
Set dbsThisOne = wspFirst.OpenDatabase("Biblio.mdb")

' Get UserName of default Workspace.
strUserName = wspFirst.UserName
' Create new Workspace.
Set wspSecond = DBEngine.CreateWorkspace("Special", strUserName, _
    "SpecialPW")
Set dbsThatOne = wspSecond.OpenDatabase("Biblio.mdb")

You don't need to specify the DBEngine object when using the OpenDatabase method. If you need to open a Database object in a separate transaction context, then you must reference a specific Workspace object. For example, the following statements are equivalent:

Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
Set dbsBiblio = Workspaces(0).OpenDatabase("Biblio.mdb")
Set dbsBiblio = OpenDatabase("Biblio.mdb")

Opening a database with attached tables doesn't automatically establish links to the specified external files or external ODBC databases; either the table's TableDef or Field objects must be referenced, or a Recordset object opened on it. If links to these tables can't be established, a trappable error occurs. You may also need permission to access the database, or the database may already be open for exclusive use by another user. In these cases, trappable errors occur.

You can also open an external database (such as FoxPro, dBASE, and Paradox) directly instead of opening a Microsoft Jet database that has links to its tables. See the OpenDatabase method for more information.

For example, to open a FoxPro database, you could use the following code.

Dim dbsFoxFiles As Database
Set dbsFoxFiles = Workspaces(0).OpenDatabase("C:\FoxFiles", _
    False,False,"FoxPro 2.5")

Note

Opening a Database object directly on an ODBC data source such as Microsoft SQLServer is not recommended because query performance is much slower than when using linked tables. Performance is not a problem with opening a Database object on an external ISAM database, such as FoxPro, Paradox, etc.

You use the Close method to remove a Database object from the Databases collection without deleting it from disk. Any open Recordset objects in the database are closed automatically when the Database object is closed.

When a procedure that declares a Database object completes execution, these local Database objects are closed along with any open Recordset objects. Any pending updates are lost and any pending transactions are rolled back, but no trappable error occurs. You should explicitly complete any pending transactions or edits and close Recordset objects and Database objects before exiting procedures that declare these object variables locally.

The first database opened is Databases(0). The Name property setting of a database is a string that specifies the path of the database file. The Connect property specifies the database type and any other parameters used to connect to external databases. You can refer to any Database object by its Name property setting using this syntax:

Databases("name")

You can also refer to the object by its ordinal number using this syntax (which refers to the first member of the Databases collection):

Databases(0)

Properties

CollatingOrder Property, Connect Property, Name Property, QueryTimeout Property, RecordsAffected Property, Replicable Property, ReplicaID Property, Transactions Property, Updatable Property, Version Property.

Methods

Close Method, CreateProperty Method, CreateQueryDef Method, CreateRelation Method, CreateTableDef Method, Execute Method, MakeReplica Method, NewPassword Method, OpenRecordset Method, Synchronize Method.

See Also

OpenDatabase Method; Appendix, "Data Access Object Hierarchy."

Specifics (Microsoft Access)

When working with data access objects from Microsoft Access, you will often need a Database object variable that represents the current database. Use the CurrentDb function to return a Database object for the database that is currently open. This Database object is automatically appended to the Databases collection.

For example, suppose you are currently working with the Northwind sample database in Microsoft Access. You can create a Database object that refers to that database by first declaring a Database object variable, then pointing it to the Database object returned by the CurrentDb function.

Dim dbs As Database
Set dbs = CurrentDb

You don't need to know the name of the database or its position in the Databases collection in order to use the current database. If you do want to know the name of the current database, check the Name property of the Database object, which contains the path and filename of the database. To find its position in the Databases collection, enumerate through the collection.

You can open only one database at a time in the Microsoft Access window. From Visual Basic code, however, you can create multiple independent Database object variables to represent multiple open databases. In this way, you can manipulate more than one database at a time from code. You can also create multiple Database object variables and point them to the current database.

Note

In your Visual Basic code, use the CurrentDb function to return a Database object that refers to the current database, rather than the DBEngine(0)(0) syntax. The CurrentDb function creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database. Using the CurrentDb function enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0) syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser environment.

Example

This example creates a new Database object and opens it (thereby appending it to the Databases collection) in the default Workspace object. Then it enumerates all the collections contained by each Database object and the properties of the new Database object and closes the new Database.

Function EnumerateDatabase () As Integer
    Dim wrkDefault As Workspace
    Dim dbsEnum As Database, dbsTemp As Database
    Dim intOBJ As Integer, intDB As Integer
    Set wrkDefault = Workspaces(0)
    Set dbsEnum = wrkDefault.CreateDatabase("Northwind.mdb", _
        dbLangGeneral)
' Enumerate all open databases.
    For intDB = 0 To wrkDefault.Databases.Count - 1
        Set dbsTemp = wrkDefault.Databases(intDB)
        Debug.Print
        Debug.Print "Enumeration of Databases: "; dbsTemp.Name
        Debug.Print
' Enumerate containers.
        Debug.Print "Container: Name, Owner"
        For intOBJ = 0 To dbsTemp.Containers.Count - 1
            Debug.Print "  "; dbsTemp.Containers(intOBJ).Name;
            Debug.Print ", "; dbsTemp.Containers(intOBJ).Owner
        Next intOBJ
        Debug.Print
' Enumerate query definitions.
        Debug.Print "QueryDef: Name"
        For intOBJ = 0 To dbsTemp.QueryDefs.Count - 1
            Debug.Print "  "; dbsTemp.QueryDefs(intOBJ).Name
        Next intOBJ
        Debug.Print
' Enumerate Recordsets.
' No output because no Recordset is open.
        Debug.Print "Recordset: Name"
        For intOBJ = 0 To dbsTemp.Recordsets.Count - 1
            Debug.Print "  "; dbsTemp.Recordsets(intOBJ).Name
        Next intOBJ
        Debug.Print

' Enumerate relationships.
        Debug.Print "Relation: Name, Table, ForeignTable"
        For intOBJ = 0 To dbsTemp.Relations.Count - 1
            Debug.Print "  "; dbsTemp.Relations(intOBJ).Name;
            Debug.Print ", "; dbsTemp.Relations(intOBJ).Table;
            Debug.Print ", "; dbsTemp.Relations(intOBJ).ForeignTable
        Next intOBJ
        Debug.Print
' Enumerate table definitions.
        Debug.Print "TableDef: Name, DateCreated"
        For intOBJ = 0 To dbsTemp.TableDefs.Count - 1
            Debug.Print "  "; dbsTemp.TableDefs(intOBJ).Name;
            Debug.Print ", "; dbsTemp.TableDefs(intOBJ).DateCreated
        Next intOBJ
        Debug.Print
    Next intDB
' Enumerate built-in properties of dbsEnum.
    Debug.Print " dbsEnum.Name: "; dbsEnum.Name
    Debug.Print " dbsEnum.CollatingOrder: "; dbsEnum.CollatingOrder
    Debug.Print " dbsEnum.Connect: "; dbsEnum.Connect
    Debug.Print " dbsEnum.QueryTimeout: "; dbsEnum.QueryTimeout
    Debug.Print " dbsEnum.Transactions: "; dbsEnum.Transactions
    Debug.Print " dbsEnum.Updatable: "; dbsEnum.Updatable
    Debug.Print
    dbsEnum.Close    ' File remains on disk.
    EnumerateDatabase = True
End Function

Example (Microsoft Access)

The following example show three ways to return a Database object in Microsoft Access. The procedure returns a Database object representing the current database, which is open in the Microsoft Access window. Next, the procedure creates another database called Newdb.mdb and saves it to disk. Then it opens an existing database called Another.mdb. Finally, it enumerates all Database objects in the Databases collection.

Sub ReferenceDatabases()
    Dim wsp As Workspace
    Dim dbsCurrent As Database, dbsNew As Database,
    Dim dbsAnother As Database, dbs As Database

    ' Return Database object pointing to current database.
    Set dbsCurrent = CurrentDb
    ' Return Workspace object pointing to current workspace.
    Set wsp = DBEngine.Workspaces(0)
    ' Create new Database object.
    Set dbsNew = wsp.CreateDatabase("Newdb.mdb", dbLangGeneral)
    ' Open database other than current database.
    set dbsAnother = wsp.OpenDatabase("Another.mdb", dbLangGeneral)
    ' Enumerate all open databases.

    For Each dbs in wsp.Databases
        Debug.Print dbs.Name
    Next dbs
End Sub