Open Password Protected DB with VBA

[Oxygen Potassium]

I have 2 databases. #1 is for user data entry and#2 is for maintenance. I want on closure of database #1 a macro to be run from database #2 and all databases to be closed. I figured out how to run the macro and close the database using this code:

Private Sub Command6_Click()
Call Shell("msaccess ""myDatabasePath.accdb"" /x Update Tables", 1)
DoCmd.Quit
End Sub

The problem I have is if I password protect database #2 it makes me manually enter the password. How can I bypass this or is there an easy fix around this?

[CJ_London]

not much point in having a password if you want to bypass it.

don't think you can do it using shell but you can using opendatabase

dim db as dao.database

set db=debengine.opendatabase("myDatabasePath.accdb",0 ,0,";PWD=abcdef")

[Oxygen Potassium]

When I tried to use this I get run time error 424 - Object required...am I missing something

[CJ_London]

I can't comment on my own air code, so unless you provide exactly what you have used, I can't really help.

[Oxygen Potassium]

This is the code I used. I don't get the error since I fixed the "DBEngine" syntax but now nothing happens when it is run...

Dim db As DAO.Database

Set db = dbengine.OpenDatabase("otherDBPath.accdb", 0, 0, ";PWD=Password")

[CJ_London]

think I may have given you the wrong object type to open - see post #2 in this link

http://www.dbforums.com/showthread.p...-in-Another-DB

[Oxygen Potassium]

Now we're getting somewhere. So here is my code now:

Private Sub Command6_Click()
Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String

strDbName = "otherDBPath"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=Password")
acc.OpenCurrentDatabase (strDbName)
acc.DoCmd.RunMacro "Update Tables"

End Sub

This opens the other db perfectly but I get an error when trying to run a macro from the 2nd one (acc.DoCmd.RunMacro line)

[CJ_London]

I get an error

what error?

[Oxygen Potassium]

Sorry the error is 2501 "RunMacro action was canceled"

[Oxygen Potassium]

If I add "DoCmd.Quit" before End Sub the coding works. However I still get the same error #2501. Maybe an error handler get rid of the error message box?

[Oxygen Potassium]

I figured it out. Here is my final coding to share:

Private Sub Command6_Click()
Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String

On Error GoTo Error_Handler:

strDbName = "OtherDBPath.accdb"
Set acc = New Access.Application
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=Password")
acc.OpenCurrentDatabase (strDbName)
acc.DoCmd.RunMacro "Update Tables", , ""
DoCmd.Quit

Error_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Number & ": " & Err.Description
Else
DoCmd.Quit
End If

End Sub

[CJ_London]

if that is what it requires and your macro has executed successfully

[NightWalker]

Oxygen,

Thank you for posting your actual solution. I was having a similar issue and your code helped me. I wish everyone would put their solutions.

Thanks,
Walker