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

I need to clean up approx modules in approx 400+ MS Access (2002 and 2007) databases. To me Perl solution appears the most efficient. However, I have not yet found a code that would allow me to e.g list Modules and Class Modules in an Access DB. In VBA, there is a collection Modules, through which you can access individual objects, but how does one get to that collection in Perl? I tried opening the DB using ADO or OLE or ODBC and cannot get to Modules.

Ideally, I'd like to do the following

  1. for each of the dbs, access the list of modules
  2. check each module if it needs processing (adding lines of code if certain conditions are met)
  3. save my changes

What I do not know is how to access the modules and if I can process them in place or if I need to export the module into text, modify it, and import it back into Access.

Any help would be greatly appreciated.
  • Comment on How to List MS Access 2007 Modules with Perl

Replies are listed 'Best First'.
Re: How to List MS Access 2007 Modules with Perl
by Corion (Patriarch) on Mar 28, 2010 at 19:27 UTC

    I recommend automating MS Access through Win32::OLE. That way, you'll get access to all the properties just like from VBA.

[reply]

      Thanks

      Modules collection is part of "CurrentProject." here's the OLE connection I'm making:

      #!perl -w

#-- code to access Ms Access objects

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft DAO';
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

my $file = q{C:\\...\\Northwind 2007.accdb};

my $appAcc = Win32::OLE->new('Access.Application', 'Quit');
my $Workspace = $appAcc->DBEngine->CreateWorkspace('', 'Admin', '');
# $appAcc->OpenCurrentDatabase 
my $Database = $Workspace->OpenDatabase($file);
# my $Database = $appAcc->OpenCurrentDatabase($file);

if (!$Database) {
    print Win32::OLE->LastError(), "\n";
} else {
    print $Database->{Name}, "\n";
    print $Database->{CurrentProject}->Modules(0)->{Name}, "\n";
}

      [download]

      The last statement causes the error:

      OLE exception from "DAO.TableDefs":

Item not found in this collection.

Win32::OLE(0.1709) error 0x80020009: "Exception occurred"
    in METHOD/PROPERTYGET "CurrentProject" at getAccessOLE.pl line 27

      [download]
[reply]
[d/l]
[select]

        There is no CurrentProject (and no CurrentDatabase) as you found out. Maybe there is Projects or some other property that is independent of a concept of "current". Look through the MS Access documentation to find where all projects are listed, and then look through that collection.

[reply]
[d/l]
[select]

        I'll keep on digging

[reply]

Back to Seekers of Perl Wisdom