1

I have a MS Access 2007 DB that is being executed via Windows NT scheduler. Basically, I created this entry in Windows Scheduler that is run every night at midnight.

I:\Case_Management_Systems\TRIAL_UNIT\Docket_Sheets_Tracking.accdb /x USER_LOG_REPORT_DELETE

The above statement opens the DB and executes the /x macro to print a report and delete some records.

Everything is fine, except that the DB has a password and when windows scheduler executes the statement, it opens the db and the DB prompts for the password. Since there is no one around to enter the password, the execution has to wait until morning when I get in and provide the password. Once the password is provided, the execution goes flawless.

I believe that before MS Access 2007 there was a /passw parameter where the password could be provided as part of the execution entry just like the macro above, but in MS Acess 2007 the /passw parameter is not allowed.

I was wondering if anyone knows a way around the password so that i can integrate it to the windows scheduler and the db can be opened at midnight when no one is here to provide the password . . . . . . .

Any help is appreciated.

Thanks in advance.

Alfredo Ramirez
  • 11
  • 1
  • 2

1 Answers1

2

There is a /pwd command line switch (see How to use command-line switches in Microsoft Access), but that is only for a ULS (user-level) security password. There is no command-line switch option for a database password.

You could create a VBScript to automate an Access session. The Application.OpenCurrentDatabase Method allows you to supply the database password when you open the db file within that session. And you can use the DoCmd.RunMacro Method to run your macro.

Finally, schedule the VBScript file in the task scheduler.

Here is a simple VBScript example to get you started. Change the values of the constants to match your situation.

Option Explicit
Const cstrDb = "C:\path\YourDbFile.mdb"
Const cstrPwd = "YourPassword"
Const cstrMacro = "YourMacroName"
Dim oAccess

Set oAccess= CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase cstrDb, False, cstrPwd
oAccess.DoCmd.RunMacro cstrMacro
oAccess.Quit
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • thanks a lot. i am not familiar with the method you described but i will certainly research it. Thanks again. – Alfredo Ramirez May 23 '13 at 18:26
  • I added a sample script. It's pretty basic, but I doubt you need anything more complicated. – HansUp May 23 '13 at 18:55
  • However, the mapped drive letter might be an obstacle. Use a UNC path instead: `\\Server\ShareName\folders_if_any\Docket_Sheets_Tracking.accdb` and make that location trusted in the trust center or digitally sign the db file. – HansUp May 23 '13 at 18:58
  • thanks a lot for the code. That helps tremendously. I will appreciate your help again. I copied the code you provided and modified accordingly see: – Alfredo Ramirez May 24 '13 at 16:24
  • Msg1 = msgbox ("Start process",,"Delete user log records") Option Explicit Const cstrDb = "I:\Case_Management_Systems\TRIAL_UNIT\Docket_Sheets_Tracking.accdb" Const cstrPwd = "Docket1" Const cstrMacro = "USER_LOG_REPORT_DELETE" Dim oAccess Set oAccess= CreateObject("Access.Application") oAccess.Visible = True oAccess.OpenCurrentDatabase cstrDb, False, cstrPwd oAccess.DoCmd.RunMacro cstrMacro oAccess.Quit Msg2 = msgbox ("So far ",,"Delete user log records") – Alfredo Ramirez May 24 '13 at 16:30
  • however, i get a compilation error message indicating line 5 char 4 expected statement . . . . . . on the very first statement Option Explicit . . . . . sorry i do not know how to put the code in here in a neat way, whenever i do enter to create a new line, it sends the comment – Alfredo Ramirez May 24 '13 at 16:32
  • Yeah, you can't use line breaks in Stack Overflow comments. `Option Explicit` requires you declare all your variables, but you added `Msg1` and `Msg2` without `Dim` --- that should fail. Can you please start by testing my simpler example and just changing the values of the constants as I suggested? That way we will both be working with the same code --- code that I've already tested. Put your enhancements on the back burner until after we can confirm the very simplest script works. – HansUp May 24 '13 at 16:55
  • Thanks a lot for your help. I managed to get a clean compilation using your code. It works like a charm and what is better, you wrote the code in such a clean and simple way that even I am able to understand what is doing. It already tested and will put in production. Just making some minor changes to the macro. I really appreciate all your help. It is working now, your code works great. – Alfredo Ramirez May 24 '13 at 17:53