>
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