Automating Access: running your own functions/passing arguments

[beartiger]

This question has to do with automating Access. I simply want automate Access to run my own VBA function in my database and pass it arguments from a script.



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

[June7]

Not sure this is an Access question. Might have better luck with answers in another forum site. However, I might be doing something similar that you could adapt.

In one case, I have a VBScript that is initiated by Windows Task Manager daily. This script opens my Access db. The Access db is set to open a form on default. This form has code in Open event to run another procedure if criteria are met.

In another situation I have code in one Access DB that executes a macro in another. That macro then runs a Function (not a Sub) procedure. I discovered that I could not call a VBA procedure from another project and that macro cannot call a Sub, hence, calling macro which calls Function.
Dim A As New Access.Application
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("\\server name\path\filename.mdb")
A.DoCmd.RunMacro "macro name"
Set A = Nothing

No idea how or if this can be done with Win32::OLE in a Perl script.

[beartiger]

Not sure this is an Access question. Might have better luck with answers in another forum site. However, I might be doing something similar that you could adapt. I have a VBScript that is initiated by Windows Task Manager daily. This script opens my Access db. The Access db is set to open a form on default. This form has code in Open event to run another procedure if criteria are met.

Thanks. The problem is I need to pass in several values to filter the query I'm running each time I run it, so something automatic like that wouldn't really work for that. In both of the things you outline, it doesn't seem any values are passed in.

I have asked this question on several forums and mailing lists, for both Perl and Access, including the Win32::Ole mailing list. No replies.

Thanks,
John

[June7]

This thread shows executing a VBA Sub from a VBScript with Run method http://www.mrexcel.com/forum/showthread.php?t=308188

Access Help says Run method can have arguments and provides code example.

I did get one project to call the Sub in another project. So I retract my earlier statement about this not being possible. I just hadn't found the right code structure. And I did get the VBScript to work.

[beartiger]

The answer, as it turns out, is to use the Application.Run method, like:

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


Best regards,
John