Run Access VBA Routing with .bat File

[jason]

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:
\Documents and Settings\Administrator\Desktop\simplestaticsimple.mdb" /
x Data

where Data is a long sub routine that does some different thing.

this current method does not work.
any help would be largely appreciated.

 

[Dirk Goldgar]

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:
\Documents and Settings\Administrator\Desktop\simplestaticsimple.mdb" /
x Data

where Data is a long sub routine that does some different thing.

this current method does not work.
any help would be largely appreciated.

Is "Data" the name of a macro, or of a VBA procedure? The /x command-line
switch will only execute macros. You could define a macro, though, that
runs your VBA procedure, and invoke that macro via the /x command-line
switch.

 

[jason]

Is "Data" the name of a macro, or of a VBA procedure?  The /x command-line
switch will only execute macros.  You could define a macro, though, that
runs your VBA procedure, and invoke that macro via the /x command-line
switch.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk,
Thank you for your response.

I am a very beginner user with Access & batch coding.
Data is the name of the sub in a module (module name=module1)

could you please give me an example of a macro that runs the vba
procedure? it is the gap in my logic that is currently holding me up.
thanks!

 

[Dirk Goldgar]

I am a very beginner user with Access & batch coding.
Data is the name of the sub in a module (module name=module1)

could you please give me an example of a macro that runs the vba
procedure? it is the gap in my logic that is currently holding me up.
thanks!

The name of the module containing the procedure doesn't matter, but I
believe the procedure has to be a Function, not a Sub. You should be able
to change your Sub to a Function just by changing the keyword "Sub" to
"Function" in its declaration.

Having done that, create a new macro. To do that in Access 2003 or before,
go to the Macros tab of the database window and click the New button. To do
it in Access 2007, go to the Create tab on the Ribbon and click the Macro
button.

In the macro you are creating, choose the RunCode action for the first (and
only) macro action, and fill in the name of your function -- *with* trailing
parentheses, like this:

Data()

.... in the Function Name argument. Then save the macro and give it a name
when prompted. You can't call it "Data", if that's the name of your
function, but you might call it "RunData". If you do name it that, then you
can revise your .bat file's command line like this:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Documents
and Settings\Administrator\Desktop\simplestaticsimple.mdb" /x RunData

(That will have been broken onto two or more lines by the newsreader, but it
should be entered all on one line.

 

[Albert D. Kallal]

A macro in ms-access and a VBA sub are VERY different things.

You can't use the command line options to run VBA direct.

(you can create a macro that calls the VBA routine and then specify that in
the startup).

However, you might as well just use windows scripting in place of that batch
file..it tends to be less hassle. Here is an article of mine that explains
how to do this:


http://www.members.shaw.ca/AlbertKallal//BatchJobs/Index.html

 

[jason]

The name of the module containing the procedure doesn't matter, but I
believe the procedure has to be a Function, not a Sub.  You should be able
to change your Sub to a Function just by changing the keyword "Sub" to
"Function" in its declaration.

Having done that, create a new macro.  To do that in Access 2003 or before,
go to the Macros tab of the database window and click the New button.  To do
it in Access 2007, go to the Create tab on the Ribbon and click the Macro
button.

In the macro you are creating, choose the RunCode action for the first (and
only) macro action, and fill in the name of your function -- *with* trailing
parentheses, like this:

    Data()

... in the Function Name argument.  Then save the macro and give it a name
when prompted.  You can't call it "Data", if that's the name of your
function, but you might call it "RunData".  If you do name it that, then you
can revise your .bat file's command line like this:

    "C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Documents
and Settings\Administrator\Desktop\simplestaticsimple.mdb" /x RunData

(That will have been broken onto two or more lines by the newsreader, butit
should be entered all on one line.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

ie:

function A()

RunCode(Data())

end function

?

 

[Dirk Goldgar]

ie:

function A()

RunCode(Data())

end function

No, that's not what I said. I think you are confused by the difference, in
Access, between macros and VBA procedures. Although Word and Excel use VBA
as their macro language, so for them a "macro" is a VBA procedure, Access
has its own -- much more limited -- macro language, that is totally distinct
from VBA. Your example above is a VBA procedure, not a macro.

Please try following the directions I gave in my previous post for creating
a macro that calls your Sub.

 

[Tom]

Jason:

Albert and Dirk have tried to answer your question directly. I'd like
to offer an alternate way of possibly accomplishing the task.

Instead of trying to run a macro at start-up, use an initial "splash
form". The approach would go something like this:

1 - your batch file executes the .mdb
2 - create a "splash form" with a timer event that opens with the
database, runs the sub, opens the "real" form you want your users to
start with, then closes itself with a timer event
3 - in the StartUp options of the database specify the splash form as
the display form
4 - if you need to pass an arguement into the sub from the batch file
use the /cmd in the batch file, then use the command() function to
retrieve it in your sub

Its not all that difficult to accomplish, and, at least IMHO, much
preferable to messing around with macros.

Good luck!

Tom