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
'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