Cannot use automation after CreateWorkspace (Access 2000)

[MackC]

am trying to use VBA to delete a form from a series of databases. Some databases are secured (the Admins user, Superv, is the owner) and some are not. I first determine if Superv is the owner of the database by opening it as Superv using CreateWorkspace. If Superv is the owner, I use DOA to give permissions to the Admin user to delete the form. Then I use automation (CreateObject) to attempt to delete the form. The problem is that when I run this program on an unsecured database, I get a 2501 error: "The DeleteObject action was canceled." Apparently Access still thinks the database is open. If I comment out the DOA part of the code, the DeleteObject works fine on an unsecured database. Any ideas about how to get around this problem. The code is below:

'First give the Admin user permission to delete the object
Dim wrk As DAO.Workspace
Dim db2 As DAO.Database
Dim doc As DAO.Document
'Allows you to log on as supervisor
'Not needed if you are currently logged on as supervisor
Set wrk = DBEngine.CreateWorkspace("SPECIAL", "superv", "hotdog")
Set db2 = wrk.OpenDatabase(strDbName)

'If the owner of the database is Superv, then this is a
'secured database. Therefore you must give Admin user
'appropriate permissions
Set doc = db2.Containers("Databases").Documents("MSysDb")
If doc.Owner = "superv" Then
MsgBox "secured"
'Give permission to open the database exclusively.
doc.UserName = "admin"
doc.Permissions = DB_SEC_DBEXCLUSIVE

'Give Admin user permission to delete the object
Set doc = db2.Containers!Forms(strUserInput)
doc.UserName = "admin"
doc.Permissions = DB_SEC_FULLACCESS
End If

Set doc = Nothing
Set db2 = Nothing
Set wrk = Nothing

'Delete the object
Dim obj As Object

'You must use automation (as opposed to DAO). Automation
'only allows you to open the database as the Admin user.
Set obj = CreateObject("Access.Application")
obj.OpenCurrentDatabase (strDbName)
obj.DoCmd.DeleteObject acForm, strUserInput
obj.CloseCurrentDatabase
Set obj = Nothing

 

[MackC]

I put the wrong error in my message, by the way. It was actually 7866, "Microsoft Access can't open the database because it is missing, or opened exclusively by another user." This database is on my C drive so I'm the only user and I do have the path and file name correct.

The error occurs at this line of code:
obj.DoCmd.DeleteObject acForm, strUserInput

 

[RoyVidar]

I'm not very fluent in usage of the DAO objects, but I see you first open a workspace and a database. I see no corresponding .Close statements, only setting the object variables to nothing.

In other settings, setting to nothing without explicitly closing the objects, might leave the application still open (check it with the task manager), so that might be the reason for the errormsg (so you might be trying to open the db twice).

Roy-Vidar

 

[MackC]

Thanks, I have added the .Close statements and I'm still having the same problem.