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

[CliffKing]

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\SnapShotExportDailySa lesReports.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

[Rich P]

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.developersdex.com ***

[Terry Kreft]

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

"CliffKing" <cl*****@msn.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

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\SnapShotExportDailySa lesReports.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

[Terry Kreft]

Dum, dum, dum...

Strike CreateObject ... insert GetObject.

Fingers running faster than head.
--
Terry Kreft

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:CP********************@karoo.co.uk...

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

"CliffKing" <cl*****@msn.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

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\SnapShotExportDailySa lesReports.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

[CliffKing]

Thanks for your help

Rich P wrote:

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.developersdex.com ***