Automating Access: running your own functions/passing arguments

[beartiger]

This question has to do with automating Access. I simply want to run
my own function in Access and pass it arguments. .

So, let's say I have written a public function called foo() in a
module in my Access database. Foo() takes a couple of arguments,
param1 and param2, the first a string, the second an int. I want to
write a script that automates Access and runs this function in my
Access database and passes it values for these parameters. What would
that look like?

I have been trying to use Win32::OLE in a Perl script. This is what I
have so far. I haven't yet tried the argument passing yet:

use strict;
use warnings;
use Win32::OLE;

my $oAccess;
my $oDatabase;

my $filename = "C:\\mydb.accdb";
$oAccess = Win32::OLE->GetActiveObject('Access.Application');

$oAccess->OpenCurrentDatabase($filename);

#$oAccess->{DoCmd}->RunCommand(myfunction());
#$oAccess->{DoCmd}->RunCommand("myfunction()");
$oAccess->{DoCmd}->RunCode('myfunction()');

The commented out lines are some of the things I've tried, but I can't
get my function to run at all.

It's not clear to me from reading the Win32::OLE documentation at CPAN
how to do this. If this is not possible with Win32::OLE, can you tell
me how I might accomplish this?

The function I'm attempting to run exports a somewhat complex Excell
spreadsheet based on queries on my database. The arguments are
variables that I plug into the queries.

Thanks for any help.

Thanks,
John

 

[David-W-Fenton]

m:

The function I'm attempting to run exports a somewhat complex
Excell spreadsheet based on queries on my database. The arguments
are variables that I plug into the queries.

I don't know for certain if it's required, but I think you need to
use Application.Run to execute a custom function via automation.
Given your code it would look like this:

$oAccess->Run("myfunction")

The Run command accepts one or more arguments, the first being the
function you're going to run, and the ensuing arguments being the
parameters you want to pass to the function you're calling.

I use Application.Run from within Access itself (for executing code
in libraries and add-ons), but have never used it with automation.
There may be some details of double vs. single quotes, parentheses
and such that differ from what I wrote above, but that's all
specific to your particular programming environment and beyond the
scope of what I know anything about.

 

[John Harrington]

I don't know for certain if it's required, but I think you need to
use Application.Run to execute a custom function via automation.
Given your code it would look like this:

  $oAccess->Run("myfunction")

The Run command accepts one or more arguments, the first being the
function you're going to run, and the ensuing arguments being the
parameters you want to pass to the function you're calling.

I use Application.Run from within Access itself (for executing code
in libraries and add-ons), but have never used it with automation.
There may be some details of double vs. single quotes, parentheses
and such that differ from what I wrote above, but that's all
specific to your particular programming environment and beyond the
scope of what I know anything about.

Thank you very much, David, for this solution. I asked this question
on another forum and got an extremely complex answer. It is indeed as
simple as you describe. I tried it with double quotes like this:

$oAccess->Run("myfunction","arg")

And it worked.

Many thanks again,
John

 

[David-W-Fenton]

m:

Thank you very much, David, for this solution. I asked this
question on another forum and got an extremely complex answer. It
is indeed as simple as you describe. I tried it with double
quotes like this:

$oAccess->Run("myfunction","arg")

And it worked.

Glad it solved your problem. I wasn't sure about the answer myself
and just Googled "automating Access" or something like that, and got
to an article on the Microsoft website that explained it all:

http://support.microsoft.com/kb/147816

It's explained under the heading "Calling Custom Procedures".