Ran a batch file or an MS Access macro shortcut without opening another instance of access

Ran a batch file or an MS Access macro shortcut without opening another instance of access

Post by CliffKin » Fri, 25 Nov 2005 03:06:49


I am trying to use Windows Task Scheduler to run a batch file for an
already open MS Access database. Below is the syntax to the batch file:

Batch file: DailySalesExport.bat
REM This runs the macro that exports the reports to the J drive
PATH = "C:\Program Files\Microsoft Office\Office\;C:\Windows\Command"
START /WAIT Msaccess.exe "D:\acesdata\SALES\Daily2005.mdb" /x
"SnapShotExportDailySales"
EXIT

Its working fine, the only problem is that its opening another instance
of MS Access and i dont want it to do that since that databse is
already open. I have to live it open because the main form has criteria
which the database uses.

I tried saving a shortcut of the macro to my computer:

D:\acesdata\SALES\BatchFiles\SnapShotExportDailySalesReports.MAM

This works fine when i double click on it from windows explorer but
when i use scheduler to run it opens another instance of ms access too!

Please help

Cliff
 
 
 

Ran a batch file or an MS Access macro shortcut without opening another instance of access

Post by Terry Kref » Fri, 25 Nov 2005 05:17:20

Look at scheduling a script file instead. You can then use createobject to
get the current instance of Access and run the macro using automation.

--
Terry Kreft

 
 
 

Ran a batch file or an MS Access macro shortcut without opening another instance of access

Post by Rich » Fri, 25 Nov 2005 14:34:03

This may not be worth much, but I have not had much luck with the
Windows scheduler. I write my own schedulers in either VB6 or VB.Net.
You have way more control over Access with a VB app. You can invoke
Access or check if an instance of Access is already running (just check
if the corresponding ldb file is alive). If it exists, then don't
invoke Access. If there is no ldb for that file, then invoke Access.
You can run subs in Access from a VB app.

Sub RunAccess()
Dim AccApp As Access.Application
AccApp = CType(CreateObject("Access.Application.10"),
Access.Application)
AccApp.OpenCurrentDatabase(Application.StartupPath & "\AccessDB1.mdb")
AccApp.Run("subGetData", d1.ToShortDateString)
AccApp.CloseCurrentDatabase()
AccApp.Quit()
End Sub

This is a sample VB.Net sub. You create the Access Object, open an
Access mdb (which happens to be in the same directory as the VB app --
Application.StartupPath), you run a sub in Access (subGetData) and pass
in an argument (a date arg here)

AccApp.Run("subGetData", d1.ToShortDateString)

When the sub is done, you close Access. The sub runs synchronously here
- means you won't go to the next line of code in the VB app until Access
finishes runnning the sub. Then you close Access down and quit the
Object.

You can set a Timer object in the VB app and set that to run based on
your specs, hourly, daily, twice a day, etc. Way more flexibility than
the Windows Scheduler. Way more reliable.


Rich

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

Ran a batch file or an MS Access macro shortcut without opening another instance of access

Post by Terry Kref » Fri, 25 Nov 2005 20:17:54

Dum, dum, dum...

Strike CreateObject ... insert GetObject.

Fingers running faster than head.


--
Terry Kreft
 
 
 

Ran a batch file or an MS Access macro shortcut without opening another instance of access

Post by CliffKin » Sat, 26 Nov 2005 02:42:06

Thanks for your help