beartiger has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm using ActiveState Perl v5.10.1. I'm on Windows XP Professional version 2003, sp3.

This question has to do with automating Access 2007 using Win32::OLE, but really the question applies to any application of Win32::OLE, I suppose.

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 Perl script using Win32::OLE that automates Access and runs this function in my Access database and passes it values for these parameters. What would that look like?

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()');

[download]

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?

Thanks for any help.

Thanks,
John

Replies are listed 'Best First'.
Re: Win32::OLE -- How do you call a function within the application?
by roboticus (Chancellor) on Jun 10, 2011 at 22:38 UTC

    beartiger:

    It's not really a perl question, as much as it is an OLE question. Generally you first have to understand the object model, then navigate the various containers until you get the item you're looking for. Then you can invoke the methods on that object.

    You're wanting to run a function you wrote in Access, so you'll need to find out where the user code is. A quick peek at the access object model documentation lists Application --> CodeData --> AllFunctions --> AccessObject. So I'd suspect that you drill down and get the AccessObject holding Foo(), and invoke the run method on it (assuming it has one). But even if you can find a way to access various objects, the object model may not give you the ability to do certain things.

    I don't use Access, nor am I much interested in spelunking through the docs, so I can't be much more help than that.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
      Thanks for the response, but I wonder if someone can provide an example of doing something like this. My problem is not so much knowing where the user code is as knowing how to pass in the values for the params.

      I know where the function is. The function is called TransposeDetails(). It's in a module called basExport. It takes two parameters, a string and an int.
[reply]
[reply]

Back to Seekers of Perl Wisdom