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 Access database engine database, 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.
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. 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.
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.
|
Methods
Name
| Description
|
|---|
|
| Closes an open Database.
|
|
| Creates a new user-defined Property object (Microsoft Access workspaces only). . |
|
|
|
|
| Creates a new Relation object (Microsoft Access workspaces only). . |
|
| Creates a new TableDef object (Microsoft Access workspaces only). . |
|
| Runs an action query or executes an SQL statement on the specified object.
|
|
| Makes a new replica from another database replica (Microsoft Access workspaces only).
|
|
| Changes the password of an existing Microsoft Access database engine database (Microsoft Access workspaces only).
|
|
| Creates a new Recordset object and appends it to the Recordsets collection. |
|
| Synchronizes any changes in a partial replica with the full replica, clears all records in the partial replica, and then repopulates the partial replica based on the current replica filters. (Microsoft Access database engine databases only.).
|
|
| Synchronizes two replicas. (Microsoft Access workspaces only).
|
Properties
Name
| Description
|
|---|
|
| Returns a value that specifies the sequence of the sort order in text for string comparison or sorting (Microsoft Access workspaces only). Read-only Long.
|
|
| Sets or returns a value that provides information about the source an open database. Read/write String.
|
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Returns the Connection object that corresponds to the database (ODBCDirect workspaces only). |
|
| Returns a Containers collection that represents all of the Container objects in the specifed database. Read-only.
|
|
| Sets or returns a 16-byte value that uniquely identifies the Design Master in a replica set (Microsoft Access workspaces only).
|
|
| Returns the name of the specified object. Read-only String.
|
|
| Returns the Properties collection of the specified object. Read-only. |
|
| Returns a QueryDefs collection that contains all of the QueryDef objects of the specified database. Read-only.
|
|
| Sets or returns a value that specifies the number of seconds to wait before a timeout error occurs when a query is executed on an ODBC data source.
|
|
| Returns the number of records affected by the most recently invoked Execute method. |
|
| Returns a Recordsets collection that contains all of the open recordsets in the for the specified database. Read-only.
|
|
| Returns a Relations collection that contains all of the stored Relation objects for the specified database. Read-only.
|
|
| Returns a 16-byte value that uniquely identifies a database replica (Microsoft Access workspaces only).
|
|
| Returns a TableDefs collection that contains all of the TableDef objects stored in the specified database. Read-only.
|
|
| Returns a value that indicates whether an object supports transactions. Read-only Boolean.
|
|
| Returns a value that indicates whether you can change a DAO object. Read-only Boolean.
|
|
| In a Microsoft Access workspace, returns the vesion of the Microsoft Jet or Microsoft Access database engine that created the database. Read-only String.
|
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 wrkAcc As Workspace
Dim dbsNorthwind As Database
Dim dbsNew As Database
Dim dbsLoop As Database
Dim prpLoop As Property
Set wrkAcc = CreateWorkspace("AccessWorkspace", "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 = wrkAcc.CreateDatabase("NewDB.mdb", _
dbLangGeneral)
Set dbsNorthwind = wrkAcc.OpenDatabase("Northwind.mdb")
' Enumerate the Databases collection.
For Each dbsLoop In wrkAcc.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
wrkAcc.Close
End Sub
This example uses CreateDatabase to create a new, encrypted Database object.
Sub CreateDatabaseX()
Dim wrkDefault As Workspace
Dim dbsNew As DATABASE
Dim prpLoop As Property
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
' 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 encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral, dbEncrypt)
With dbsNew
Debug.Print "Properties of " & .Name
' Enumerate the Properties collection of the new
' Database object.
For Each prpLoop In .Properties
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
Next prpLoop
End With
dbsNew.Close
End Sub