Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remote database (Access 2000)

    I want to rename a macro in a remote database database :

    Set db = DBEngine.Workspaces(0).OpenDatabase("C:benew.mdb", dbDriverComplete, False, "; PWD=" & strPassword)
    DoCmd.Rename "Macro2", acMacro, "Macro1"

    However i receive the message that Microsoft cant find the object macro1.
    What is wrong with my command ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remote database (Access 2000)

    OpenDatabase is a DAO instruction. DoCmd.Rename is an Access instruction that always acts on the database that is currently open in the interface, not on a database you open using DAO.

    To rename a macro, you can start a second instance of Access, open the database, rename the macro and quit the instance.

    Sub RenameMacro()
    Dim strPassword As String
    Dim app As New Access.Application
    On Error GoTo ErrHandler

    strPassword = "Topsecret"
    app.OpenCurrentDatabase "C:benew.mdb", , strPassword
    app.DoCmd.Rename "Macro2", acMacro, "Macro1"

    ExitHandler:
    On Error Resume Next
    app.Quit
    Set app = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remote database (Access 2000)

    Thanks Hans really grateful for that it was a great help for me

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remote database (Access 2000)

    Dear Hans

    when i tried the code i got the folowing error with pointing to the line
    OpenCurrentDatabase
    Compile error : wrong number of arguments or invalid properrt assignment
    maybe it is because i am trying to open the database form another database?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remote database (Access 2000)

    Did you notice that there are two commas between the database name and the password?

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remote database (Access 2000)

    Yes,i have copied and pasted:
    Public Function RenameMacro()
    Dim strPassword As String
    Dim app As New Access.Application
    On Error GoTo ErrHandler

    strPassword = "secret"
    app.OpenCurrentDatabase "C:benew.mdb", , strPassword
    app.DoCmd.Rename "Macro2", acMacro, "Macro1"

    ExitHandler:
    On Error Resume Next
    app.Quit
    Set app = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function
    The error point to the words OpenCurentdatabase.But i am trying to open another database from the current database? I do not find myself in the database callled New. I am trying to open it from another database.Maybe this is the reason ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remote database (Access 2000)

    I'm sorry, I am using Access 2002; in this version you can supply a password in OpenCurrentDatabase. This option is not available in Access 2000.

    MSKB article How to open a password-protected database through Automation in Access 2000 describes a workaround.

    Here is a modified version:

    Public Function RenameMacro()
    Dim strPassword As String
    Dim strDatabase As String
    Dim app As New Access.Application
    Dim dbs As DAO.Database
    On Error GoTo ErrHandler

    strDatabase = "c:benew.mdb"
    strPassword = "Secret"
    Set dbs = app.DBEngine.OpenDatabase(strDatabase, , , ";PWD=" & strPassword)
    app.OpenCurrentDatabase strDatabase
    app.DoCmd.Rename "Macro2", acMacro, "Macro1"

    ExitHandler:
    On Error Resume Next
    dbs.Close
    Set dbs = Nothing
    app.Quit
    Set app = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remote database (Access 2000)

    Yes, thank you! it works great !

Posting Permissions

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