Database Object

Description

A Database object represents an open database.

Remarks

You use the Database object and its methods and properties to manipulate an open database. In any type of database, you can:

Use the Execute method to run an action query.
Set the Connect property to establish a connection to an ODBC data source.
Set the QueryTimeout property to limit the length of time to wait for a query to execute against an ODBC data source.
Use the RecordsAffected property to determine how many records were changed by an action query.
Use the OpenRecordset method to execute a select query and create a Recordset object.
Use the Version property to determine which version of a database engine created the database.

With a Microsoft Jet database (.mdb file), you can also use other methods, properties, and collections to manipulate a Database object, as well as create, modify, or get information about its tables, queries, and relationships. For example, you can:

Use the CreateTableDef and CreateRelation methods to create tables and relations, respectively.
Use the CreateProperty method to define new Database properties.
Use the CreateQueryDef method to create a persistent or temporary query definition.
Use MakeReplica, Synchronize, and PopulatePartial methods to create and synchronize full or partial replicas of your database.
Set the CollatingOrder property to establish the alphabetic sorting order for character-based fields in different languages.

In an ODBCDirect workspace, you can:

Use the Connection property to obtain a reference to the Connection object that corresponds to the Database object.

You use the CreateDatabase method to create a persistent Database object that is automatically appended to the Databases collection, thereby saving it to disk.

You don't need to specify the DBEngine object when you use the OpenDatabase method.

Opening a database with linked tables doesn't automatically establish links to the specified external files or Microsoft Jet-connected ODBC data sources. You must either reference the table's TableDef or Field objects or open a Recordset object. If you can't establish links to these tables, a trappable error occurs. You may also need permission to access the database, or another user might have the database opened exclusively. In these cases, trappable errors occur.

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

Note Opening a Database object directly on a Microsoft Jet-connected ODBC data source, such as Microsoft SQL Server, is not recommended because query performance is much slower than when using linked tables. However, performance is not a problem with opening a Database object directly on an external ISAM database file, such as FoxPro, Paradox, and so forth.

When a procedure that declares a Database object has executed, 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.

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.

Note You can open the same data source or database more than once, creating duplicate names in the Databases collection. You should assign Database objects to object variables and refer to them by variable name.

Properties

CollatingOrder property, Connect property, Connection property, Name property, QueryTimeout property, RecordsAffected property, Replicable property, ReplicaID property, ReplicationConflictFunction property, Transactions property, Updatable property, V1xNullBehavior property, Version property.

Methods

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

See Also Database property, OpenDatabase method.

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 file name 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 an existing Database object in the default Workspace object. Then it enumerates the Database collection and the Properties collection of each Database object.

Sub DatabaseObjectX()

    Dim wrkJet As Workspace
    Dim dbsNorthwind As Database
    Dim dbsNew As Database
    Dim dbsLoop As Database
    Dim prpLoop As Property

    Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "", dbUseJet)

    ' Make sure there isn't already a file with the name of
    ' the new database.
    If Dir("NewDB.mdb") <> "" Then Kill "NewDB.mdb"

    ' Create a new database with the specified
    ' collating order.
    Set dbsNew = wrkJet.CreateDatabase("NewDB.mdb", _
        dbLangGeneral)
    Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

    ' Enumerate the Databases collection.
    For Each dbsLoop In wrkJet.Databases
        With dbsLoop
            Debug.Print "Properties of " & .Name
            ' Enumerate the Properties collection of each
            ' Database object.
            For Each prpLoop In .Properties
                If prpLoop <> "" Then Debug.Print "    " & _
                    prpLoop.Name & " = " & prpLoop
            Next prpLoop
        End With
    Next dbsLoop

    dbsNew.Close
    dbsNorthwind.Close
    wrkJet.Close

End Sub

Example (Microsoft Access)

The following example shows 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 reference to current database.
    Set dbsCurrent = CurrentDb
    ' Return reference to default 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")
    ' Enumerate all open databases.
    For Each dbs in wsp.Databases
        Debug.Print dbs.Name
    Next dbs
    For Each dbs In wsp.Databases
        Set dbs = Nothing
    Next dbs
    Set wsp = Nothing
End Sub