Task Scheduler

[janetb]

On XP SP2 machine, I'm trying to get an Access DB to open automatically every
day at a set time. Here's the syntax I got from:
http://support.microsoft.com/?id=210111

myTest.bat is the following:
c:
cd\Program Files\Microsoft Office\Office10\MSACCESS.exe
f:\APPS95\List\listAuto.mdb

Task Scheduler set to execute myTest.bat

Nothing happens. Tried putting quotes around directories with spaces.
Tried taking out the onOpen, and executing a Macro appending " /x Macro1"
above. Nothing.

Yet the same account is using the Task Scheduler to copy files across the
network every two minutes and it's working fine.

I've got the start-up options set to open a form which fires the event
onOpen which does the following (and if I manually open the mdb, everything
works fine):

Dim strFileName As String
strFileName = "f:\apps95\list\fhc" & Format$(Date, "mm-dd-yy") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"qryFHCxlsRpt", strFileName, True, ""

Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
strMsg = "Detailed summary report for the following docs: " & vbCrLf

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select Nz([REGULAR-MD], 'Unknown Doc') from qryFHCxlsRpt",
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
strMsg = strMsg & rs.GetString(adClipString)

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

I'm very new to this, so if anyone has any ideas or a better way to do this,
I'd appreciate it.

 

[Dirk Goldgar]

On XP SP2 machine, I'm trying to get an Access DB to open
automatically every day at a set time. Here's the syntax I got from:
http://support.microsoft.com/?id=210111

myTest.bat is the following:
c:
cd\Program Files\Microsoft Office\Office10\MSACCESS.exe
f:\APPS95\List\listAuto.mdb

Task Scheduler set to execute myTest.bat

Nothing happens. Tried putting quotes around directories with spaces.
Tried taking out the onOpen, and executing a Macro appending " /x
Macro1" above. Nothing.

Yet the same account is using the Task Scheduler to copy files across
the network every two minutes and it's working fine.

I've got the start-up options set to open a form which fires the event
onOpen which does the following (and if I manually open the mdb,
everything works fine):

Dim strFileName As String
strFileName = "f:\apps95\list\fhc" & Format$(Date, "mm-dd-yy") &
".xls" DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "qryFHCxlsRpt", strFileName, True, ""

Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
strMsg = "Detailed summary report for the following docs: " &
vbCrLf

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select Nz([REGULAR-MD], 'Unknown Doc') from
qryFHCxlsRpt", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
strMsg = strMsg & rs.GetString(adClipString)

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

I'm very new to this, so if anyone has any ideas or a better way to
do this, I'd appreciate it.

I don't see where you got that syntax from the web page you cited, but
it looks wrong to me. This line:

cd\Program Files\Microsoft Office\Office10\MSACCESS.exe
f:\APPS95\List\listAuto.mdb

doesn't execute anything -- it just tries to change the directory (to an
invalid path). How about this:

C:\Program Files\Microsoft Office\Office10\MSACCESS.exe
f:\APPS95\List\listAuto.mdb

Note that I entered the above all on one line, but the newsreader will
have broken it onto two lines in posting.

 

[Rob Oldfield]

A couple of things to try...

Add a pause command at the end of your batch file. This will leave the DOS
window open so you can see any error messages.

Replace the f: with the actual UNC. i.e.
\\servername\share\APPS95\List\listAuto.mdb I think your problem might be
that the drive isn't set up for the account that you're using to run the
scheduled task.

Check the file c:\windowsfolder\schedlgu.txt for any information. It's a
log of scheduled tasks.

 

[Graham Mandeno]

Hi Janet

C:\Program Files\Microsoft Office\Office10\MSACCESS.exe
f:\APPS95\List\listAuto.mdb

Because the path C:\Program Files\... has spaces in it, you will need to
enclose everything up to msaccess.exe in quote marks. Same goes for the
path to the MDB file, if it contains spaces.

Also, you can use the /X switch on the command line to execute a named macro
when the database opens. This is particularly useful when you want to open
the database, perform some automated function, and close it again.

So your command line might read:

"C:\Program Files\Microsoft Office\Office10\MSACCESS.exe"
"f:\My APPS95\List\listAuto.mdb" /X mcrUpdateTime

(all on one line, of course)

 

[Dirk Goldgar]

Hi Janet


Because the path C:\Program Files\... has spaces in it, you will need
to enclose everything up to msaccess.exe in quote marks. Same goes
for the path to the MDB file, if it contains spaces.

Yes, Graham is right; I just checked.

 

[Janet Barnett via AccessMonster.com]

Hey ya Rob,
Thanks for the reply, again.

I put the execution in the Run statement and everything works ducky now.
Sorry to be so slow - it's the first time I've done anything quite like
this.

'Preciate the assistance,
Janet