Creating a Workspace

When you first refer to a Workspace object, or one of its collections, objects, methods, or properties, you automatically create the default workspace, which can be referenced using the following syntaxes: DBEngine.Workspaces(0), DBEngine(0), or simply Workspaces(0).

The default workspace is given the name #Default Workspace#. In the absence of user- and group-level security, the default workspace's UserName property is set to Admin. If security is implemented, the UserName property is set to the name of the user who logged on.

You don't have to do anything to begin using a Microsoft Jet workspace; Access creates one by default unless you explicitly create an ODBCDirect workspace. To use an ODBCDirect workspace, you either set the DBEngine's DefaultType property to dbUseODBC, or set the CreateWorkspace method's Type property to dbUseODBC, when you create the workspace.

The basic procedure for creating a new workspace is as follows:

1. Create the workspace, using the DBEngine's CreateWorkspace method.

2. Append the new workspace to the Workspaces collection.

You can use a workspace without appending it to the Workspaces collection, but you must refer to it using the object variable to which it was assigned. You will not be able to refer to it through the Workspaces collection until it is appended.

The following example demonstrates how to create both a Microsoft Jet workspace and an ODBCDirect workspace, and print their Name properties:

Dim wsJet As DAO.Workspace Dim wsODBC As DAO.Workspace

'Create a new Microsoft Jet workspace Set wsJet = DBEngine.CreateWorkspace( _

"myJetWS", strUserName, strPassword,


'Create a new ODBCDirect workspace

Set wsODBC = DBEngine.CreateWorkspace( _

"myODBCWS", strUserName, strPassword,


'Append the workspaces to the collection Workspaces.Append wsJet Workspaces.Append wsODBC

'Print the names of all the workspaces Debug.Print "wsJet.Name: " & wsJet.Name Debug.Print "wsODBC.Name: " & wsODBC.Name

'myJetWS 'myODBCWS

'Clean up wsODBC.Close wsJet.Close

Set wsODBC = Nothing

Set wsJet = Nothing

If you just want to use the default workspace, you can either refer to it as DBEngine(0), or create a reference to it in the same way you create references to other Access or DAO objects.

'Create a reference to the default workspace Set wsJet1 = DBEngine(0)

Debug.Print "wsJeti.Name: " & wsJet1.Name '#Default Workspace#

Since you're not creating a new workspace object, there is no need to append it to the Workspaces collection.

Finally, there is one other way to create a new workspace. To maintain compatibility with previous versions of DAO, Access 2003 still provides the DefaultWorkspaceClone method.

'Create a clone of the default workspace

Set wsJet2 = Application.DefaultWorkspaceClone

Debug.Print "wsJet2.Name: " & wsJet2.Name '#Clone Access#

The DefaultWorkspaceClone method creates a clone (identical copy) of the default workspace, whatever it happens to be. The cloned workspace takes on properties identical to those of the original, with the exception of its Name property, which is set to #Clone Access#.

You would use the DefaultWorkspaceClone method where you want to operate two independent transactions simultaneously without needing to prompt the user again for the username and password.

Was this article helpful?

+1 -1

Post a comment