A Workspace object defines a named session for a user. It contains open databases and provides mechanisms for simultaneous transactions and, in Microsoft Access workspaces, secure workgroup support.
Remarks
A Workspace is a non-persistent object that defines how your application interacts with data by using the Microsoft Access database engine. Use the Workspace object to manage the current session or to start an additional session. In a session, you can open multiple databases or connections, and manage transactions. For example, you can:
- Use the Name, UserName, and Type properties to establish a named session. The session creates a scope in which you can open multiple databases and conduct one instance of nested transactions.
- Use the Close method to terminate a session.
- Use the OpenDatabase method to open one or more existing databases on a Workspace.
- Use the BeginTrans, CommitTrans, and Rollback methods to manage nested transaction processing within a Workspace and use several Workspace objects to conduct multiple, simultaneous, and overlapping transactions.
When you first refer to or use a Workspace object, you automatically create the default workspace, DBEngine.Workspaces(0). The settings of the Name and UserName properties of the default workspace are "#Default Workspace#" and "Admin," respectively. If security is enabled, the UserName property setting is the name of the user who logged on.
When you use transactions, all databases in the specified Workspace are affected— even if multiple Database objects are opened in the Workspace. For example, you use a BeginTrans method, update several records in a database, and then delete records in another database. If you then use the Rollback method, both the update and delete operations are canceled and rolled back. You can create additional Workspace objects to manage transactions independently across Database objects.
You can create Workspace objects with the CreateWorkspace method. After you create a new Workspace object, you must append it to the Workspaces collection if you need to refer to it from the Workspaces collection.
You can use a newly created Workspace object without appending it to the Workspaces collection. However, you must refer to it by the object variable to which you have assigned it.
To refer to a Workspace object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
DBEngine.Workspaces(0)
DBEngine.Workspaces("name")
DBEngine.Workspaces![name]
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.
|
Methods
Name
| Description
|
|---|
|
| Begins a new transaction. Read/write Database.
|
|
| Closes an open Workspace.
|
|
| Ends the current transaction and saves the changes.
|
|
| Creates a new Database object, saves the database to disk, and returns an opened Database object (Microsoft Access workspaces only). |
|
| 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.
Opens a Connection object on an ODBC data source (ODBCDirect workspaces only). |
|
| Opens a specified database in a Workspace object and returns a reference to the Database object that represents it. |
|
| Ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.
|
Properties
Name
| Description
|
|---|
|
| Returns a Connections collection that represents the current connections in the specified Workspace. Read-only.
|
|
| Returns a Databases collection that represents the open databases in the specified Workspace. Read-only.
|
|
| 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.
Sets or returns the type of cursor driver used on the connection created by the OpenConnection or OpenDatabase methods (ODBCDirect workspaces only). |
|
| Sets or returns a value that indicates whether multiple transactiond that involve the same Microsoft Access database engine-connected ODBC data source are isolated (Microsoft Access workspaces only).
|
|
| Sets or returns the number of seconds before an error occurs when you attempt to log on to an ODBC database.
|
|
| Returns or sets the name of the specified object. Read/write String if the object has not been appended to a collection. Read-only String if the object has been appended to a collection.
|
|
| Returns the Properties collection of the specified object. Read-only. |
|
| Sets or returns a value that indicates the operational type or data type of an object. Read-only Integer.
|
Example
This example creates a new Microsoft Access Workspace object and appends it to the Workspaces collection. It then enumerates the Workspaces collections and the Properties collection of the Workspace object.
Sub WorkspaceX()
Dim wrkNewAcc As Workspace
Dim wrkLoop As Workspace
Dim prpLoop As Property
' Create a new Microsoft Access workspace.
Set wrkNewAcc = CreateWorkspace("NewAccessWorkspace", _
"admin", "", dbUseJet)
Workspaces.Append wrkNewAcc
' Enumerate the Workspaces collection.
For Each wrkLoop In Workspaces
With wrkLoop
Debug.Print "Properties of " & .Name
' Enumerate the Properties collection of the new
' Workspace object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop
End With
Next wrkLoop
wrkNewAcc.Close
End Sub
This example uses the CreateWorkspace method to create a Microsoft Access workspace. It then lists the properties of theworkspace.
Sub CreateWorkspaceX()
Dim wrkAcc As Workspace
Dim wrkLoop As Workspace
Dim prpLoop As Property
DefaultType = dbUseJet
' Create an unnamed Workspace object of the type
' specified by the DefaultType property of DBEngine
' (dbUseJet).
Set wrkAcc = CreateWorkspace("", "admin", "")
' Enumerate Workspaces collection.
Debug.Print "Workspace objects in Workspaces collection:"
For Each wrkLoop In Workspaces
Debug.Print " " & wrkLoop.Name
Next wrkLoop
With wrkAcc
' Enumerate Properties collection of Microsoft Access
' workspace.
Debug.Print _
"Properties of unnamed Microsoft Access workspace"
On Error Resume Next
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " = " & prpLoop
Next prpLoop
On Error GoTo 0
End With
wrkAcc.Close
End Sub
The following example shows how to use a transaction in a Data Access Objects (DAO) workspace.
Public Sub TransferFunds()
Dim wrk As DAO.Workspace
Dim dbC As DAO.Database
Dim dbX As DAO.Database
Set wrk = DBEngine(0)
Set dbC = CurrentDb
Set dbX = wrk.OpenDatabase("e:\books\acc2007vba\myDB.accdb")
On Error GoTo trans_Err
'Begin the transaction
wrk.BeginTrans
'Withdraw funds from one account table
dbC.Execute "INSERT INTO tblAccounts ( Amount, Txn, TxnDate ) SELECT -20, 'DEBIT', Date()", dbFailOnError
'Deposit funds into another account table
dbX.Execute "INSERT INTO tblAccounts ( Amount, Txn, TxnDate ) SELECT 20, 'CREDIT', Date()", dbFailOnError
'Commit the transaction
wrk.CommitTrans dbForceOSFlush
trans_Exit:
'Clean up
wrk.Close
Set dbC = Nothing
Set dbX = Nothing
Set wrk = Nothing
Exit Sub
trans_Err:
'Roll back the transaction
wrk.Rollback
Resume trans_Exit
End Sub