Using VBScript to open password protected db

[aziz rasul]

Does anyone know what to write on a VB Script file to open a password protected db and run a macro?

 

[ecawilkinson]

Aziz,

put the following into a VBscript file - obviously substituting the dummy names for you own:

   Dim db 
   Dim strDbName
   dim strPassword
   dim strMacro
   strMacro = "YourMacro"
   strdbName = "c:\yourpath\yourDB.mdb"
   strPassword = "YourPassword"
   Set acc = createobject("Access.Application")

   ' this temporarily changes the macros security to low to avoid messages.
   'onlt works with access 2000 onwards
   if acc.syscmd(7) >= 10 Then
	acc.AutomationSecurity = 1
   End if
   
   'if you do not need to see Access delete this line
   acc.Visible = True

   Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=" & strPassword)
   acc.OpenCurrentDatabase strDbName
   acc.docmd.runmacro strMacro
   db.Close

This will open the db, run the macro then immediately shut it down. I'm not sure if it works with anything less than Access 2002. If you have a prior version, you could try the lines that refer to automation security. This is a literal solution to your problem based on very little info. I'm sure I could come up with a better designed answer if you let me know what you are trying to do.

HTH,
Chris

 

[aziz rasul]

Many thanks. That did it.

 

[JReagan]

Aziz,

put the following into a VBscript file - obviously substituting the dummy names for you own:

   Dim db 
   Dim strDbName
   dim strPassword
   dim strMacro
   strMacro = "YourMacro"
   strdbName = "c:\yourpath\yourDB.mdb"
   strPassword = "YourPassword"
   Set acc = createobject("Access.Application")
 
   ' this temporarily changes the macros security to low to avoid messages.
   'onlt works with access 2000 onwards
   if acc.syscmd(7) >= 10 Then
    acc.AutomationSecurity = 1
   End if
 
   'if you do not need to see Access delete this line
   acc.Visible = True
 
   Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=" & strPassword)
   acc.OpenCurrentDatabase strDbName
   acc.docmd.runmacro strMacro
   db.Close

This will open the db, run the macro then immediately shut it down. I'm not sure if it works with anything less than Access 2002. If you have a prior version, you could try the lines that refer to automation security. This is a literal solution to your problem based on very little info. I'm sure I could come up with a better designed answer if you let me know what you are trying to do.

HTH,
Chris

Where would you normally insert this code? On a form with a button, marco...? I can't seem to get the window to stay visible, it still closes imediately, do I need to change my reference?

JB

 

[aziz rasul]

The code is placed in a vbscript file. Open Notepad and insert your code and save the file with the extension vbs. All you do now is to double click your vbs file which executes the code. You can now schedule the vb file to run at specific times if you wish.

It's a long time since I did this so hopefully this helps.

 

[JReagan]

Would it still work the same if I put this in a module in another Access file? I got it where the window opens but then closes immediately after. Here is the code I have, any help why you think it might not be working would be greatly appreciated.

**********Code Start**********
Option Compare Database
Public Function OpenDB()

Dim db
Dim strDbName
Dim strPassword
strDbName = "L:\New Reporting DBs\Reports.accdb"
strPassword = "MyPassword"
Set acc = CreateObject("Access.Application")

' this temporarily changes the macros security to low to avoid messages.
'onlt works with access 2000 onwards
If acc.SysCmd(7) >= 10 Then
acc.AutomationSecurity = 1
End If

acc.Visible = True

Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=" & strPassword)
acc.OpenCurrentDatabase strDbName

End Function
**********Code End**********

Thanks,
JB

 

[aziz rasul]

But should you not have the macro line etc.

acc.docmd.runmacro strMacro
   db.Close

The way you have the code set up just now, it should simply open the db. However if the code is in a separate Access file I would have thought it would work but can't be positive on that unless I try it.

Have you tried using a VB Script file and doing it that way just to establish that it works and then see if the same code will work on a separate db?

 

[JReagan]

I took out the line to run the macro to see if I can get just the file to open first. I just put the code in a .vbs file and it did the exact same thing when I ran it (Open and closed immediately). Let me know if you have any luck with this. Thanks.

JB

 

[aziz rasul]

In that case, I'm stumped.