title: accessing MSysObjects from Access 2010 accdb-format db
body: <p>Windows XP Pro
  ActiveState Perl 5.14.4
  M$ Access 2010</p>
  
  <p>I have an Access db in accdb format which contains a number
  of stored queries. I'd like to extract them to flat files using Perl.</p>
  
  <p>The names of the queries appear in [MSysObjects].[Name] while the queries
  themselves appear to be stored in the MSysQueries somehow.</p>
  
  <p>But my problem is upstream from that - I can't read either of these tables.
  I've confirmed when I open the db directly the username is "Admin".</p>
  
  <p>Googling turned up <a href="http://t1134.codeinpro.us/q/5080f5c04f1eba38a4d1547b" rel="nofollow">http://t1134.codeinpro.us/q/5080f5c04f1eba38a4d1547b</a> ,
  which explains "Admin" does not have SELECT permission to MSysObjects so I need
  to execute a DDL statement to GRANT SELECT ON MSysObjects TO Admin</p>
  
  <p>More googling turns up <a href="http://www.connectionstrings.com/access-2010" rel="nofollow">http://www.connectionstrings.com/access-2010</a> , which says
  to enable certain administrative statements like GRANT I need to add 
      Uid=Admin; Pwd=; ExtendedAnsiSQL=1;
  to my connection string</p>
  
  <p>And that same page has a blurb to specifically state where the workgroup file is
  located
      SystemDB=C:\mydatabase.mdw
  in the connection string.
  More on this in a moment.</p>
  
  <p>So I've got this</p>
  
  <pre><code>#! /home/gnu/bin/perl
  #
  
  package main;
  
  use strict;
  use autouse 'Data::Dumper' =&gt; qw(Dumper);
  use DBI;
  use DBD::ODBC;    # works for both access and sql server
  use Win32::ODBC;
  use OLE;
  use Win32::OLE;
  
  # prototypes
  sub _setUpDatabaseHandle_Access_Local_EnableAdminStatements($);
  sub setPermissionsOnTables($);
  sub readTableMSysObjects($);
  
  #globals
  my $debug = 1;
  
  my $targetDbFile  = undef;
  my $dbh_C = undef;
  
  print "version of DBD::ODBC   is ".$DBD::ODBC::VERSION."\n";
  print "version of Win32::ODBC is ".$Win32::ODBC::VERSION."\n";
  print "version of Win32::OLE  is ".$Win32::OLE::VERSION."\n";
  
  $targetDbFile  = "C:/putyer/msaccessdb/here.accdb";
  
  $dbh_C = _setUpDatabaseHandle_Access_Local_EnableAdminStatements( $targetDbFile);
  
  setPermissionsOnTables( $dbh_C);
  readTableMSysObjects( $dbh_C);
  
  $dbh_C-&gt;disconnect() if( defined( $dbh_C));
  
  exit(0);
  #-------------------------------
  #  when you use perl to open an .accdb  Access database the Uid is always Admin
  #  But user Admin doesn't have SELECT permission on MSysObjects by default you must first
  #  grant the permission on the object for the user
  #  to enable certain admin commands like CREATE USER, GRANT, REVOKE, DEFAULTS when using CREATE TABLEs
  sub _setUpDatabaseHandle_Access_Local_EnableAdminStatements($)
  {
    my ( $dbFile) = @_;
  
    my $errorHit = 0;
  
    my $conn_str = "DBI:ODBC:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=".$dbFile;
    $conn_str   .= "; Uid=Admin; Pwd=; ExtendedAnsiSQL=1";
  
    my $dbh = DBI-&gt;connect( $conn_str);
    if( !defined( $dbh)) {
      print "db connection failed - ".$DBI::errstr."\n";
      $errorHit = 1;
    }
  
    print "connection _setUpDatabaseHandle_Access_Local_EnableAdminStatements failed\n" if( $errorHit);
  
    return $dbh;
  }
  #-------------------------------
  sub setPermissionsOnTables($)
  {
    my ( $dbh) = @_;
  
    print "entering setPermissionsOnTables\n" if $debug;
  
    my $errorHit = 0;
  
    my $q   = undef;
    my $sth = undef;
  
    unless( $errorHit) {
      $q = "GRANT SELECT ON MSysObjects TO Admin";
      $sth = $dbh-&gt;prepare($q);
      if( !defined( $sth)) {
        print "sth failed to define - ".$DBI::errstr."\n";
        $errorHit = 1;
      } else {
        $sth-&gt;execute();                        # &lt;===  FAILS HERE
        if( my $errorMsg = $dbh-&gt;errstr ) {
          print "select statement failed - ".$errorMsg."\n";
          $errorHit = 1;
        }
      }
    }
  
    unless( $errorHit) {
      $q = "GRANT SELECT ON MSysQueries TO Admin";
      $sth = $dbh-&gt;prepare($q);
      if( !defined( $sth)) {
        print "sth failed to define - ".$DBI::errstr."\n";
        $errorHit = 1;
      } else {
        $sth-&gt;execute();
        if( my $errorMsg = $dbh-&gt;errstr ) {
          print "select statement failed - ".$errorMsg."\n";
          $errorHit = 1;
        }
      }
    }
  
    print "exiting  setPermissionsOnTables with errorHit=".$errorHit."\n" if $debug;
    return $errorHit;
  }
  #-------------------------------
  sub readTableMSysObjects($)
  {
    my ($dbh) = @_;
  
    print "entering readTableMSysObjects\n" if $debug;
  
    my $errorHit = 0;
  
    my $q   = undef;
    my $sth = undef;
  
    unless( $errorHit) {
      $q = "SELECT * FROM MSysObjects";  # remember that db nulls come over as perl undefs
      $sth = $dbh-&gt;prepare($q);
      if( !defined( $sth)) {
        print "sth failed to define - ".$DBI::errstr."\n";
        $errorHit = 1;
      } else {
        $sth-&gt;execute();
        if( my $errorMsg = $dbh-&gt;errstr ) {
          print "select statement failed - ".$errorMsg."\n";
          $errorHit = 1;
        } else {
          if( $sth-&gt;rows == 0 ) {
            print "select statement returned 0 rows\n";
            $errorHit = 1;
          } else {
            my $row_hashref = undef;
            while( $row_hashref = $sth-&gt;fetchrow_hashref() ) {
              print Dumper( $row_hashref);
            }
          }
        }
        $sth-&gt;finish();
      }
    }
  
    print "exiting  readTableMSysObjects\n" if $debug;
    return $errorHit;
  }
  </code></pre>
  
  <p>which fails when attempted to execute the first GRANT with the infamous 
      Cannot open the Microsoft Access database engine workgroup information file
  message. </p>
  
  <p>So it appears I need this file too, which I can't seem to find on my box.
  Or could/should I use a different connection? Like one of these?</p>
  
  <pre><code>#-------------------------------
  sub _setUpDatabaseHandle_Access_Local_ADO_ACE($)
  {
    my ( $dbFile) = @_;
  
    my $errorHit = 0;
  
    my $conn_str = "DBI:ADO:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=".$dbFile;
    $conn_str   .= "; Persist Security Info=False";
  # print $conn_str."\n";
  
    my $dbh = DBI-&gt;connect( $conn_str);
    if( !defined( $dbh)) {
      _pushErrorMsg("db connection failed - ".$DBI::errstr);
      $errorHit = 1;
    }
  
    print "connection _setUpDatabaseHandle_Access_Local_ACE failed\n" if( $errorHit);
  
    return $dbh;
  }
  #-------------------------------
  sub _setUpDatabaseHandle_Access_Local_ADO($)
  {
    my ( $dbFile) = @_;
  
    my $errorHit = 0;
  
    my $conn_str = "DBI:ADO:File Name=".$dbFile;
  
    my $username = undef;
    my $password = undef;
  
    my $dbh = DBI-&gt;connect( $conn_str, $username, $password);
    if( !defined( $dbh)) {
      _pushErrorMsg("db connection failed - ".$DBI::errstr);
      $errorHit = 1;
    }
  
    print "connection _setUpDatabaseHandle_Access_Local_ADO failed\n" if( $errorHit);
  
    return $dbh;
  }
  </code></pre>
  
  <p>Any pointers/hints/constructive critisms and especially examples are all welcome.</p>
  
  <p>TIA,</p>
  
  <p>Still-learning Steve</p>