Workspace Object

Description

A Workspace object defines a named session for a user. It contains open databases and provides mechanisms for simultaneous transactions and, in Microsoft Jet workspaces, secure workgroup support. It also controls whether you are going through the Microsoft Jet database engine or ODBCDirect to access external data.

Remarks

A Workspace is a non-persistent object that defines how your application interacts with data — either by using the Microsoft Jet database engine, or ODBCDirect. 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.
Further, using a Microsoft Jet database, you can establish security based on user names and passwords:

  • Use the Groups and Users collections to establish group and user access permissions to objects in the Workspace.
  • Use the IsolateODBCTrans property to isolate multiple transactions that involve the same Microsoft Jet-connected ODBC database.
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.

To establish an ODBCDirect Workspace object, and thereby avoid loading the Microsoft Jet database engine into memory, set the DBEngine object's DefaultType property to dbUseODBC, or set the type argument of the CreateWorkspace method to dbUseODBC.

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]

Properties

DefaultCursorDriver property, IsolateODBCTrans property, LoginTimeout property, Name property, Type property, UserName property.

Methods

BeginTrans, CommitTrans, Rollback methods, Close method, CreateDatabase method, CreateGroup method, CreateUser method, OpenConnection method, OpenDatabase method.

See Also

Transactions property.

Example

This example creates a new Microsoft Jet Workspace object and a new ODBCDirect Workspace object and appends them to the Workspaces collection. It then enumerates the Workspaces collections and the Properties collection of each Workspace object. See the methods and properties of the Workspace object or Workspaces collection for additional examples.

Sub WorkspaceX()

    Dim wrkNewJet As Workspace
    Dim wrkNewODBC As Workspace
    Dim wrkLoop As Workspace
    Dim prpLoop As Property

    ' Create a new Microsoft Jet workspace.
    Set wrkNewJet = CreateWorkspace("NewJetWorkspace", _
        "admin", "", dbUseJet)
    Workspaces.Append wrkNewJet

    ' Create a new ODBCDirect workspace.
    Set wrkNewODBC = CreateWorkspace("NewODBCWorkspace", _
        "admin", "", dbUseODBC)
    Workspaces.Append wrkNewODBC

    ' 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

    wrkNewJet.Close
    wrkNewODBC.Close

End Sub