Creating a Macro to Autorun with Scheduler

[nmarano]

We have an app built with Access 97, that we archive often. What we are
currently doing is opening the form, clicking on a button which calls a
module that runs a function called slf_monthend(). My question is can I set
up a Macro which would open the module and run the function called
slf_monthend() by using the scheduler to run the Macro?

 

[Steve Schapel]

Nmarano

Hey, this is very similar to another post!

If you want to use Windows Task Manager to schedule the task, rather
than the approach I suggested in the other thread, the command line in
the scheduler will be something the equivalent of this...

"C:\PathToAccess\Msaccess.exe" "C:\PathToDB\YourDB.mdb" /x NameOfMacro

 

[nmarano]

Thanks Steve.

Nmarano

Hey, this is very similar to another post!

If you want to use Windows Task Manager to schedule the task, rather
than the approach I suggested in the other thread, the command line in
the scheduler will be something the equivalent of this...

"C:\PathToAccess\Msaccess.exe" "C:\PathToDB\YourDB.mdb" /x NameOfMacro

 

[Morris]

Hi Steve,

Just tried your suggestion below. Created a .cmd file with the macro to
run. Tried several macros to run a report, delete records or display table
but they did not work. When I double click on the .cmd file, I see something
flash on the screen but there is no action taken. I have a seperate post
asking how to run an Access macro from outside Access. What am I missing?
Any help would be much appreciated.

 

[Steve Schapel]

Morris,

What is the exact syntax of the CMD file?

 

[Morris]

Thanks for your help Steve. We just realized that the path for Access was
incorrect in the cmd file. Appreciate the quick response and your help. I
tested in the cmd window and was able to see the error on the screen and then
debug.

On a seperate topic, do you know what needs to be done for a service to
access an MS Access file from a workgroup from an XP computer via ODBC? I
have a service that works well to get the data when running on 2000 but have
problems running on XP.

 

[Steve Schapel]

Morris,

No, sorry, I don't know about that. Someone else may chip in, or else a
repost to the microsoft.public.access.odbcclientsvr newsgroup might get
you better help.

 

[Morris]

Thanks Steve.

In a .cmd file, do you know of a way to rename a file, or replacing a file
using the file name but concatenating with the current date? So for example
the file was abc.cvs then a copy of the file could be named abd022605.cvs.
Bottom line is that after I complete the macro, I need to rename the file to
some unique name.

Thanks again for your help.

 

[Steve Schapel]

Morris,

Just guessing here, but maybe one of the things your macro does is
export a query using the TransferText action? If so, why not just
include the current date in the name of the exported file, rather than
trying to rename it afterwards?

 

[Morris]

Hi Steve,

You are correct in your assumption. The .csv file will be automatically
created each day and then used to import into Access using the macro.
Because it is automatically created, the file name needs to be the same.
This is also required so the macro can run automatically using the same file
name. At the end of the macro I want to rename the file to make sure that
the next day, if a new file is not created then we do not use the same file.

Any thoughts on how I can rename the file?

 

[Steve Schapel]

You are correct in your assumption. The .csv file will be automatically
created each day and then used to import into Access using the macro.

No, actually my assumption was completely wrong. I thought you were
exporting the .csv file from an Access database, not importing it.

Because it is automatically created, the file name needs to be the same.

Well, not really, it depends on where it is coming from and how it is
being created... but I won't try any more guesswork this time.

This is also required so the macro can run automatically using the same file
name.

Well, this depends on the macro you are using.

At the end of the macro I want to rename the file to make sure that
the next day, if a new file is not created then we do not use the same file.

Makes sense.

Any thoughts on how I can rename the file?

Easy in a VBA procedure, using the Name <original file> As <new name>
method, but as far as I know not possible with a macro.

 

[JohnK]

No, actually my assumption was completely wrong. I thought you were
exporting the .csv file from an Access database, not importing it.


Well, not really, it depends on where it is coming from and how it is
being created... but I won't try any more guesswork this time.


Well, this depends on the macro you are using.


Makes sense.


Easy in a VBA procedure, using the Name <original file> As <new name>
method, but as far as I know not possible with a macro.

I do a lot of export/import type databases. I found a useful way is to
leave the file named a specific filename. When I export I include a
datestamp field in the file (just a field with date()). On the import side I
pull the file into an import table as part of a macro. I have a query that
checks for the date. If the date is incorrect (mine is usually date must =
date()-1) the records are deleted from the import table and the appropriate
message box tells why. If the dates are what was expected the rest of the
import proceeds. Maybe klunky but it works.

 

[Steve Schapel]

Thanks, John, this is a useful suggestion.