Running an Access Front End from a Batch File

Question

Windows 10, Access 2016

Batch file started by Scheduled Task

Batch file executes if I double click it but fails every time with the event 300 error when it is started via Scheduled Tasks:

   The last time you opened "Create_Client_List.accde" it caused a serious error. Do you still want to open it?

   P1: 700164

   P2: 16.0.4813.1000

Since this is a scheduled task I do not have the option to answer this question so the task hangs until Scheduled Tasks kills it

The Scheduled Task fails whether I am logged into the computer or not - it is intended to run when no users are logged in

Batch File:

Copy "\\X_Server\Access\Back End\Client List.accdb" "\\X_Server\Access\Back End\Client List_NEW.<g class="gr_ gr_808 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="808" id="808">accdb</g>"
Start /Wait "C:\Program Files\Microsoft Office\Office16\MSAccess.exe" "\\X_Server\Access\Front End\Create_Client_List.<g class="gr_ gr_809 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="809" id="809">accde</g>"
Start /Wait "C:\Program Files\Microsoft Office\Office16\MSAccess.exe" "\\X_Server\Access\Front End\Compact_Client_List.<g class="gr_ gr_810 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="810" id="810">accde</g>"

The copy command works

Answer 1

Where does the "event 300 error" come from? From the Event Log?

Anyway, Access should only be run in this situation where the user is logged in but has locked his/her session. Microsoft advises even against this, because Access may require user interaction. I have already done this successfully.

A possibility is that Access wants to display a message. Running an Access application with the Scheduler requires that you make 100% sure that no message boxes will pop up. That means, at the minimum, to place error handling into each and every procedure/function/method/property that logs the error and continues without a message box.

Matthias Kläy , Kläy Computing AG

 

 

Answer 2

What does the db do when it is opened?  Run a Macro/Function, query, ...?

 

I'd probably comment out all the code and slowly renable it and test between each permutation until I could identify the exact source of the issue.

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

The 300 <g class="gr_ gr_27 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="27" id="27">event</g> was caused by a failed execution record in the Registry - apparently from a prior failed test. I deleted the failed execution records from the Registry. The Access database still halts immediately after it is executed - with no error message or status indication.

The database consists of a single form that is opened when the database is opened The form has an On Open event that sets up the variables for several tables then updates a table.

There are no MsgBox statements and all VBA processes have an On Error statement to trap and record Access errors to a table<g class="gr_ gr_493 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="493" id="493">.The</g> batch file runs if I execute it directly but fails when run as a Scheduled Task whether I am logged in or signed out.

I added a MsgBox statement as the first statement in the On Open event - the message is not displayed when the batch file is run as a Scheduled Task so the problem occurs before any VBA code is executed.

Answer 4

You should *not* open a form when you want to run Access unattended. Instead, create a macro, say "UnattendedCalculation" with a RunCode action that executes a public function that does all of the work. In your batch file, start Access with the command

"<PathToAccess>\MSACCESS.EXE" "<PathToYourDB>\YourDB.accdb" /x UnattendedCalculation

All work must be done in code alone.

Matthias Kläy, Kläy Computing AG

Answer 5

I deleted the form and added an AutoExec Macro that runs the VBA code and

added the /x UnattendedCalculations switch

<g class="gr_ gr_93 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="93" id="93">Same</g> result, the Access database starts then stops immediately with no indication of why

Answer 6

I created a stub database that links some of the tables then executes VBA code to:

   Set CurrentDatabase = CurrentDB()

   Set TableName = CurrentDatabase.OpenRecordSet("Table Name")  

   DoCmd.Quit

It runs and quits with just the Set CurrentDatabase command but does not stop running if the Set Tablename command is included

This code is common to many Access applications and runs ok if executed directly rather than as a scheduled task

Answer 7

Stub database will open a linked table if on the C Drive but fails if the linked table is on a network drive

Unable to find a Event Log entry that specifically identifies the failure reason

The problem appears to be network access permissions

The Task Schedule is saved with a network admin user id

Batch file runs is executed directly but not when run as a scheduled task

Answer 8

Matthias

Why do you say "You should *not* open a form when you want to run Access unattended"?

I have been doing that successfully for years.

Bob

---

Bob Alston

Answer 9

Are you using a mapped drive somewhere? If you're running your scheduled task with a different user account and that drive mapping is not set for that user then you might experience this problem.  Can you run your batch file interactively from a command prompt while logged in as this network admin user?

-Bruce

Answer 10

All tables on single mapped network drive

I am able to run batch file interactively using same login

Problem temporary solved by copying files from network drive to C: drive folder, running batch file then copying files back to network