0

I want to run vba code at specified time say at 6.30 p.m. everyday. I tried scheduler with batch file but it didnt work due to privillage issue. I want to use ontime function for that in access. I am done with outlook part to send mail but issue is to run at everyday at specified time.

Thanks in advance.

Pankil Patel
  • 5
  • 6
  • 3
    Do you know what specific privilege issue you encountered? – Jeremy Sep 05 '18 at 16:44
  • 1
    Are you planning to leave the access DB open 24/7/365? – Nathan_Sav Sep 05 '18 at 16:49
  • @ jeremyHi, i am having priviledge issue of operation code 2, my workplace is not allowing to run batch based job....when i manually run my batch file it runs perfectly and create outlook mail but the same thing is not happening with scheduler, i am not able to do schedule on "highest priviledge", so i thought of using ontime function – Pankil Patel Sep 05 '18 at 17:02
  • @nathan_sav, i can open the same on daily basis so that during day it can run once, say i can make it open from 9 a.m to 9 p.m and want to run at say 6 p.m., once in a day – Pankil Patel Sep 05 '18 at 17:04
  • If you are doing this from your workplace, and the privilege is denied, and it is legitimate work, then can your IT department modify your privileges? email is a sensitive thing, and has extra security because it has been abused in the past. – Jeremy Sep 05 '18 at 17:06
  • Check [my answer here](https://stackoverflow.com/questions/40910634/schedule-ms-access-module/40911304#40911304) it may help – Thomas G Sep 05 '18 at 17:14
  • Hi, jeremy and thomas G, let me try to solve priviledge issue with IT. It seems task scheduler is better option....thank you so very much for prompt help....will get back in case of further help on this – Pankil Patel Sep 05 '18 at 17:23

2 Answers2

0

Here is how you do it:

  • Set up your VBA code as function (not sub).
  • Create a macro that runs that function and then closes the database.
  • Create a command prompt batch file that runs your MS Access file with the /x switch (see example below).
  • Set up the batch file as a scheduled task on your server or any computer you want that VBA code to run on. If not a server make sure it stays on and there is no power saving on network card, no sleep/suspend. Make sure the scheduled task's user has any required privileges.

You can set up a scheduled task to run with admin privileges. Generally if the user set up as the user for the task can run it, then the scheduled task will run as well. It does not have to be the user creating the scheduled task. If necessary create a user account with the required permissions and use that.

Batch file example:

"C:\Program Files\Microsoft Office\Office16\MSACCESS.EXE" "C:\Databases\MyDatabase.accdb" /x Daily
EXIT

This will run Access 2016, 64-bit, open the MyDatabase.accdb file and run the macro named "Daily" .

As an addition: It is possible to do scheduled tasks and use Outlook as a sender. But if you don't need to use Outlook as a sender you can make your life a little easier and use Swithmail.

If you absolutely need to use Outlook, check this: https://support.microsoft.com/en-us/help/3189806/a-program-is-trying-to-send-an-e-mail-message-on-your-behalf-warning-i

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
  • Hello guys, i am done with priviledge part and my code is running perfectly but a new issue i encountered is my db is opened all the time, now i want to make some changes in my module but it shows me error that i cannot save changes probably due to db is opened. I am not able to close it via task manager it shows me you nees admin rights to perform the task. As i am on company machine, i do not have admin rights. – Pankil Patel Sep 06 '18 at 06:28
  • What is probably happening is that either you missed the step where your macro has to close the database as its final act, or whatever code you used to accomplish that is not working properly. You can test that by running your batch script manually and making sure the database closes at the end with no errors. This is not the kind of thing you can troubleshoot without admin rights. You will probably need some help by your company's IT support. – SunKnight0 Sep 06 '18 at 15:23
0

Just use Windows Task Scheduler. Even if you have a batch file, which you don't need for this kind of thing, you would still fire it off with WTS.

  1. Press the Windows + R keys to open the Run dialog, type taskschd.msc, and press Enter. NOTE: This will open Task Scheduler. This file is located at: C:\Windows\system32\taskschd.msc.

  2. While having Task Scheduler Library selected in the left pane, click/tap on Create task in the far right pane. (see screenshot below)

  3. Under the General tab, type in the "Name" of the program you want to create this shortcut for. (see screenshot below) NOTE: This will be the name of the task. As an example, I will use CCleaner for that program. Make note of this name, you will need it later in step 12 and 14 below.

  4. Check the Run with highest privileges box. (See screenshot below)

  5. Click/tap on the Configure for drop down box to open it, and select Windows® 7, Windows Server™ 2008 R2 or the Windows you are currently in. (see screenshot above and below) NOTE: This is like compatibility mode. If the program does not support running in Windows 7 normally, then you can select the OS that is does support running in instead to have it run in compatibility mode as if it was running in that OS instead of Windows 7.

    Elevated Program Shortcut without UAC Prompt - Create-drop_down_box.jpg

  6. Click/tap on the Actions tab, then on the New button. (see screenshot below)

  7. In the Program/script: box in the Actions tab, copy and paste the path below. (see screenshot below step 9) NOTE: Thank you to chillz for the updated steps 7 and 8.

    %windir%\System32\cmd.exe

  8. In the Add arguments (optional): box in the Actions tab, type in the argument below. (see screenshot below step 9)

NOTE: Substitute Task Name in the argument below with the name you entered in step 3 (ex: CCleaner) within quotes, and substitute Full Path of Program in the argument below with the actual full path of the program's .exe file within quote. c start "Task Name" "Full Path of Program" For example: Code: c start "CCleaner" "C:\Program Files\CCleaner\CCleaner64.exe"

  1. Click/tap on OK. (see screenshot below)

  2. Click/tap on the Conditions tab, and uncheck the Stop if the computer switches to battery power box first, then uncheck the Start the task only if the computer is on AC power box. (see screenshots below) NOTE: This step is mostly for laptops, so that you will be able to run the program when the laptop is running on battery power.

  3. Click/tap on OK. (See screenshots above)

  4. You will now see your new task created with the name (ex: CCleaner) you gave it in step 3. Close Task Scheduler. (see screenshot below)

  5. Right click or press and hold on an empty area of your desktop, then click/tap on New and Shortcut.

  6. Type in the target below for the location field, then click/tap on the Next button. (see screenshot below) NOTE: Subsitute task-name in the target with the name (ex: CCleaner) within quotes that you used in step 3.

    schtasks /run /tn "task-name"

    For example: For my task name CCleaner I used from step 3, I would type: schtasks /run /tn "CCleaner"

  7. Type in a name you want for the shortcut (ex: CCleaner), then click/tap on the Finish button. (see screenshot below)

  8. Right click on the new shortcut (ex: CCleaner) on the desktop, then click/tap on Properties.

  9. Click/tap on the Shortcut tab, and on the Change Icon button. (see screenshot below)

  10. Click/tap on OK. (see screenshot below)

  11. Click/tap on the Browse button. (see screenshot below)

  12. Navigate to the .exe file of the program that you have created this shortcut with, select it, and click/tap on Open. (see screenshot below) NOTE: If you have a icon of your own that you would prefer to use instead, then navigate to where you have that icon saved at instead.

  13. Select the icon you want for the shortcut, then click/tap on OK. (see screenshot below)

  14. Click/tap on OK. (see screenshot below)

  15. You can now use this elevated shortcut to run at startup in your "Startup" folder, run at startup as a task, Pin to Start Menu, Pin to Taskbar, assign a keyboard shortcut, or move to where you like for easy use.

  16. When you run this new elevated shortcut, you will now no longer have to click/tap on YES to approve the UAC prompt to allow the program run as administrator.

https://www.sevenforums.com/tutorials/11949-elevated-program-shortcut-without-uac-prompt-create.html

Finally, see this.

https://support.office.com/en-us/article/create-a-macro-that-runs-when-you-open-a-database-98ba1508-dcc6-4e0f-9698-a4755e548124

ASH
  • 20,759
  • 19
  • 87
  • 200