Use PERL to run subroutine in MS Access database

[bdbmeag]

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";
}

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

Any help or examples would be greatly appreciated.

Thanks!

- Bruce

 

[sycoogtit]

When you say that neither of them work, what exactly do you mean? They give you no output? They die with errors?

Can you post that access file? I don't have access.

--

http://MyUniversityTutor.com

 

[bdbmeag]

The test .mdb file is just a blank .mdb file with a single subroutine in module1 with one line of code: msg "OK!"

When I execute the perl scripts they compile but the msgbox is not displayed.

 

[sycoogtit]

I don't use windows, so I've never used Win32::OLE and I don't know what a subroutine in an mdb file would look like. If you can post the file, then I can start playing around and see if I can figure something out.

--

http://MyUniversityTutor.com

 

[PinkeyNBrain]

I've got code that does this and works fairly consistently. Reservations are being prestated here as I'm not an expert with Access nor WIN32::OLE. As a result some of the routines are not very elegant in error recovery. To avoid blasting a huge subroutine in here, I'll try to copy/paste in the basic lines of code that do most of the work. In general if you see a 'return' statement, assume that it's fall out of the code being copied from a subroutine:

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
$mdb_file = $_[0] ; # ex. G:/data_dir/data_src_003.mdb
$DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$mdb_file;UID=;PWD=;";
$conn = Win32::OLE->new("ADODB.Connection");
return 0 if (! $conn);
# If some other error hit, it's already printed, just find it and return.
$error = Win32::OLE->LastError() ;
if ($error) { $conn = 0 ; return 0 ;};
$rs   = Win32::OLE->new("ADODB.Recordset");
return 0 if (! $rs) ;
$error = Win32::OLE->LastError() ;
if ($error) { $conn = 0 ; return 0 ;};
# At this point, $rs is our link into the access file.
return $rs ;

$table_name = $_[0] ; # ex. customer_info
# NOTE: See 'WHERE CLAUSE' comment later on 
$sql = "SELECT * FROM $table_name" ;
$rs->Open($sql, $conn, 2, 2);
$error = Win32::OLE->LastError() ;
if ($error) { $rc = 0 ; return 0 ;};
return 1 ;

And finally:

until ($rs->EOF) {
   $cust_name = $rs->Fields('Cust_Name')->value ;
   print "Cust Name>$cust_name<\n";
   $rs->MoveNext ; # This one is really important unless you like inf-loops.
}
$rs->Close ;
$conn->Close ;

This should get you to at least your first variable output. Some additional notes:
PERMISSIONS - Under the access database tools -> security -> User and Group Permissions ... I've got things opened up quite a bit.
WHERE CLAUSE - The method I'm using to read is fairly slow. I've got some DBs at 500K+ lines and it (was) taking 4-5 minutes to read them (and I only needed about 50K of them). I've started to play around with stuff like: $sql = "SELECT * FROM $table_name WHERE LAST_CUST_DATE like '2008*'" ; and have been getting some very good speed improvements (point here - if there is something you're compensating for in perl that is native to Access, use the Access version if you can figure it out)
VARIABLE/COLUMN NAMES - With the method I've got above, if $rs->Fields($column_name)->value is not valid, what I have will crash. Subsequently I have to be cautious about table changes across column names.

Let me know if you're able to get the above to work - I'll add in a couple of gee-whiz loops I've come up with that (I think) work pretty good at reading in data.

 

[PinkeyNBrain]

Sorry - Guess I didn't read the initial posting close enough. You're looking to run a macro. Never tried that one.

 

[bdbmeag]

thanks pinknbrain,

yeah, i'm looking to run a macro. however, the code for setup may be of use. thanks.

sycoogtit,

i'm unable to load the database at this time.

thanks,

- bruce