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

Hi, I'm working in Windows Vista, using ActivePerl 5.8.7 Build 813, using MS Access 2007, and executing the .pl file in MKS KornShell.

I'm trying to use Win32:OLE to open an existing MS Access .mdb file and run a public subroutine:

Most of my searches have yielded very little on this; the examples I have found primary focus on Excel and Word. The examples with Access focus primarily on using DBI to query data to/from the Access database.

Following are two pieces of code I've pieced together from what I've been able to find. Neither of them work and I'm unable to figure out what is incorrect.

Attempt #1: 

#!/usr/bin/perl

use strict; 
use warnings; 
use Win32::OLE::Const 'Microsoft Access'; 

my $Filename = "s:/mdbtest.mdb"; 

my $loAccess;   # Access Object
my $loDatabase;    # Database Object

eval {$loAccess = Win32::OLE->GetActiveObject('Access.Application')};
die "Access not installed" if $@;
unless (defined $loAccess) {
  $loAccess = Win32::OLE->new('Access.Application','Quit')
    or die "Unable to start Access";
}

$loDatabase = $loAccess->DBEngine->OpenDatabase($Filename);
if (Win32::OLE->LastError) {
  print "Unable to Open Access Database, LastError returned ",
        Win32::OLE->LastError, "\n";
}

[download]
Attempt #2: 
#!/usr/bin/perl

use strict; 
use warnings; 
use Win32::OLE::Const 'Microsoft Access'; 

my $Filename = "s:/dev/devbatch/bruce/mdbtest.mdb"; 

#my $Access = Win32::OLE->new('Access.Application', 'Quit'); 
#my $Access = Win32::OLE->getActiveObject('Access.Application');

my $Access = Win32::OLE->GetObject($Filename);
my $Workspace = $Access->DBEngine->CreateWorkspace('', 'Admin', ''); 
$Access -> OpenCurrentDatabase($Filename);

$Access->{'Visible'}=1;

$Access->RunTest();
#$Access -> DoCmd -> RunMacro("RunTest");

##$Access -> Quit();

[download]
Any help or examples would be greatly appreciated. Thanks! - Bruce

Replies are listed 'Best First'.
Re: Use Win32::OLE to run vba subroutine in MS Access
by Corion (Patriarch) on Oct 05, 2008 at 21:39 UTC

    You don't tell us how they fail for you. A common approach to automating all Office applications is to record the sequence of commands as a VBA macro and then converting that macro to Perl. Hence, start recording a macro, then press ALT+F8 or whatever the "Run Macro" hotkey is, run the macro, press "Stop Recording". Then press ALT+F11 and inspect the generated code for your macro.

[reply]
[d/l]
[select]
Re: Use Win32::OLE to run vba subroutine in MS Access
by NetWallah (Canon) on Oct 05, 2008 at 22:51 UTC
    If all you need to do is run an Access macro, perl may not be the most expedient/appropriate solution. Here is an alternative: 
    msaccess.exe  "c:\My Documents\Databases\MyDatabase.mdb" /x MyMacro

    [download]

         Have you been high today? I see the nuns are gay! My brother yelled to me...I love you inside Ed - Benny Lava, by Buffalax

[reply]
[d/l]
      Hi, Where do you run this line of code (msaccess.exe "c:\My Documents\Databases\MyDatabase.mdb" /x MyMacro)? .......................................... Here is update on where I am ... The error message I get is "Couldn't open database s:/mdbtest! at s:\mdbtest.pl line 20. 
      #!/usr/bin/perl

use strict; 
use warnings; 

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Access';
Win32::OLE->Option(Warn => 0);

my $oAccess;
my $oDatabase;

my $filename = "s:/mdbtest.mdb";  # $ARGV[0];
print $filename."\n";
$oAccess = Win32::OLE->new('Access.Application') or die qq{Couldn't st
+art new Access instance!};

# Open Access File
$oDatabase = $oAccess->DBEngine->OpenDatabase($filename) or die qq{Cou
+ldn't open database $filename!};

$oDatabase->Run("RunTest");

print "$filename"."\n";

$oDatabase->Save;

undef $oDatabase;
undef $oAccess;
$oAccess->Quit();

      [download]
      Thanks,
[reply]
[d/l]
        I was suggesting you abandon your entire program, and run the following instead, either as a bat file, or directly from the command prompt: 
        msaccess.exe  s:\mdbtest.mdb /x RunTest

        [download]
        In case you want to pursue the perl route, please add an error message display to your code. This will make it easier to identify the problem: 
        # Open Access File
$oDatabase = $oAccess->DBEngine->OpenDatabase($filename) 
  or die qq{Couldn't open database $filename!\n} . 
     Win32::OLE->LastError();

        [download]

             Have you been high today? I see the nuns are gay! My brother yelled to me...I love you inside Ed - Benny Lava, by Buffalax

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom