Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot use automation after CreateWorkspace (Access 2000)

    I 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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cannot use automation after CreateWorkspace (Access 2000)

    I don't think I understand. If you are using a secured workspace to change the permissions on the form, why not just delete it at that point instead of messing with the permissions? There is no reason you can't delete a form from another database, even a secured one, as long as you have exclusive access to it at the moment and have opened a workspace with the required permissions.

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Are you trying to run this in a single routine? If so, part of your problem is that you don't have code in there to close the secured workspace and drop the connection to the database, unless you snipped that part to limit the size of the post.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jul 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot use automation after CreateWorkspace (Access 2000)

    Q1. I thought you could not delete a form using DAO, but had to use automation.
    Q2. I am running this as a part of the same routine, but I thought setting wrk and db2 to nothing would close the workspace and the database.

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cannot use automation after CreateWorkspace (Access 2000)

    Sorry, I was thinking of a table when I wrote that. It sounds like you still have an active link. Are you using a form to hold a connection open, perhaps?
    Charlotte

  5. #5
    Lounger
    Join Date
    Jul 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot use automation after CreateWorkspace (Access 2000)

    This code is behind a form, but the form is bound to a table in the currrent database, not the remote database. By trial and error I have gotten the program to work. Instead of:
    Set obj = CreateObject("Access.Application")
    obj.OpenCurrentDatabase (strDbName)
    I use:
    Set obj = GetObject(strDbName)
    But I would still like to know why GetObject works and CreateObject doesn't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •