MS-Access / Getting Started

---

The DBEngine Object

The DBEngine object is a property of the Access Application object, and represents the top-level object in the DAO model. The DBEngine object contains all the other objects in the DAO object hierarchy, yet unlike many of the other DAO objects, you can't create additional DBEngine objects.

The DBEngine object contains two major collections - Workspaces and Errors - which are described in this section because they relate so closely to the DBEngine object.

The Workspaces Collection

A workspace is a named user session that contains open databases and provides the facility for transactions and (depending on the database format) user-and group-level security. As you can have more than one workspace active at any time, the Workspaces collection is the repository for all the workspaces that have been created.

You use the Microsoft Access workspace to access Microsoft Access database engine databases (ACCDB files created in Access 2007), Microsoft Jet databases (MDB files created in previous versions), and ODBC or installable ISAM data sources through the Microsoft Access database engine. For a list of the collections, objects, and methods supported by Microsoft Access workspaces.

The Workspace object contains three different object collections. These are Databases, Groups, and Users. Note that the Groups and Users collections are hidden in Access 2007 and you'll need to show them in the Object Browser before they will appear using IntelliSense. Each of these collections is described in later sections.

ODBCDirect

In addition to the Microsoft Access workspace, previous versions of DAO supported a second type of Workspace object called ODBCDirect. ODBCDirect workspaces are used against ODBC data sources such as SQL Server. Beginning with Office 2007, Microsoft is no longer shipping RDO that enabled this type of workspace. As a result, ODBCDirect is no longer supported in DAO. You will see a runtime error if you try to create an ODBCDirect workspace using the CreateWorkspace method.

Subsequently, because you cannot create ODBCDirect workspaces, calling the OpenConnection method will cause a runtime error, and the Connections collection of the DBEngine object will not contain any Connection objects.

Creating a Workspace

If you have Microsoft Jet databases that use Jet security to help secure objects, there may be times when you need to provide access to them from unsecured databases. For such an occasion, you can create a new Workspace object to provide the username and password for the secured database.

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)
• simply Workspaces(0)

The default workspace is given the name #Default Workspace#. In the absence of user- and grouplevel 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 Access workspace; Access creates one by default.

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 a Microsoft Access workspace, and print the Name property:

Dim wsAccess As DAO.Workspace
Dim strUserName As String
Dim strPassword As String

'Set the user name and password
strUserName = "Admin"
strPassword = ""

'Create a new Microsoft Access workspace
Set wsAccess = DBEngine.CreateWorkspace( _
    "myAccessWS", strUserName, strPassword, dbUseJet)

'Append the workspaces to the collection
Workspaces.Append wsAccess

'Print the name of the workspace
Debug.Print "wsAccess.Name: " & wsAccess.Name 'myAccessWS

'Clean up
wsAccess.Close
Set wsAccess = Nothing

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 wsAccess1 = DBEngine(0)
Debug.Print "wsAccess1.Name: " & wsAccess1.Name '#Default Workspace#

Because 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 2007 still provides the DefaultWorkspaceClone method.

'Create a clone of the default workspace
Set wsAccess2 = Application.DefaultWorkspaceClone
Debug.Print "wsAccess2.Name: " & wsAccess.Name '#CloneAccess#

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 #CloneAccess#. You can change this name if you choose.

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.