If you don’t create and reference a new Workspace object when you are using the CreateDatabase method, the default Workspace object is used. The effect of using the default Workspace object depends on which host application is running the Visual Basic code. For example, in Microsoft Access, the default Workspace object is identical to the one used to log on to the application database that is executing the code. The new database is owned by the user account that is used to log on to Microsoft Access before the CreateDatabase method is used. If the Logon dialog box hasn’t been activated in Microsoft Access, the database owner is the default Admin user account.
See Also For information about using Microsoft Jet security, see Chapter 10, “Managing Security.”
In Visual Basic version 5.0, the UserName property of the default Workspace object is set to Admin and the Name property is set to #DefaultWorkspace#. Because the user doesn’t log on to the database independently of the code executed by a Visual Basic application, there is no user Workspace object for Visual Basic to inherit as there is with Microsoft Access. This situation also applies when you are using the CreateDatabase method in Visual Basic for Applications code with Microsoft Word, Microsoft Excel, or Microsoft PowerPoint.
Additionally, for any application that is using DAO, you can set the DefaultUser and DefaultPassword properties of the DBEngine object to specify the default user account and password that will be used when a new Workspace object is created.
Note Microsoft Jet 3.5 creates Microsoft Jet Workspace objects by default. However, if you specify the DefaultType property of the DBEngine object as dbUseODBC, or specify dbUseODBC when using the CreateWorkspace method, an ODBCDirect Workspace object is created. The Name and UserName property issues discussed in this section don’t apply to ODBCDirect Workspace objects. For more information about using ODBCDirect features, see Chapter 9, “Developing Client/Server Applications.”
To create a secured Workspace object, provide a user name other than the default for the user argument of the CreateWorkspace method. You can then create a new database in the Databases collection for that Workspace object. The owner of the new database is the user specified when the workspace is created. Microsoft Jet security requires that the owner of a secured database create the database while logged on to the appropriate secured Workspace object. The owner of the database can be specified only when the database is first created.
The owner of the new database can establish permissions on objects in the new database. To determine the owner of a database, check the value of the Owner property for the MSysModules Document object.
Important If you want to secure a database, don’t create the Workspace object by using “Admin” as the value of the user argument of the CreateWorkspace method. The Admin user is the default user, and if the Admin user is the owner of a database, any user can open the database and alter objects or permissions on objects. To create a secured database, you must always use a nondefault user account.
The following example creates a secured workspace and then creates a database within that workspace. In this example, strWrkName is the name of the new secured workspace, strUserName is the name of the user account under which the workspace is created, strPassword is the user’s password, and strDbName is the name of the new database.
Dim wrkSecured As Workspace Dim dbs As Database ' Create secured workspace. Set wrkSecured = _ DBEngine.CreateWorkspace(strWrkName, strUserName, strPassword) ' Create new secured database. Set dbs = _ wrkSecured.CreateDatabase(strDbName, dbLangGeneral, dbVersion30)
If you specify a user account that doesn’t yet exist, Microsoft Jet returns an error when you attempt to create the new workspace. To create a new user account automatically when an unknown user name is passed to the CreateWorkspace method, you can handle the error by including the following code in the error handler. You create the new user account on the default Workspace object. In this example, strGroupName is the name of the group to which the new user is added. The code also adds the new user to the Users group.
Dim wrk As Workspace, usr As User
Dim strPID As String, intC As Integer
' Return reference to default workspace.
Set wrk = DBEngine(0)
strPID = InputBox("Enter an alphanumeric PID between 4 and 20 " _
& "characters. Be sure to write this PID down in a safe " _
& "place, along with your user name and password.")
' Create user in Users collection of default workspace.
Set usr = wrk.CreateUser(strUserName, strPID, strPassword)
wrk.Users.Append usr
' Create user in Users collection of specified group.
Set usr = wrk.Groups(strGroupName).CreateUser(strUserName)
wrk.Groups(strGroupName).Users.Append usr
Note The previous examples are taken from the CreateSecuredDatabase function, which is available on the companion CD-ROM.
A new database can be created programmatically with DAO only; there is no SQL DDL method of creating a new database. A database, however, can’t be deleted programmatically through DAO. If you want to delete a database programmatically, use the Visual Basic for Applications Kill statement to remove the file at the file-system level of the operating system.