MS Access stores all the tables and schema in a single .mdb file. It's more than possible that an OLE call exists to extract the schema by table. For example in ADO.Net, OleDbConnection.GetOleDbSchemaTable() method can be used to extract a schema.

you did not mention

• which os your using (implied by win32::odbc)
  
• assuming win32, what access drivers do you have?
  
• is the db encrypted? (no mention on site about this but could be a problem)
  
• what version of access files your using (mbt supports version 3/4)
  

method 1
DBIx::XML_RDB by matts - straight from the man docs ...

sql2xml.pl -sn myserver -driver myDriver -uid user -pwd seekrit -table user -output users.xml

here's a tute with code.

• download/install mbtools on *nix box.
  
• follow description how to generate schema - read docs.
  
• use redirection to get results
  

the above should really only be attempted for those who have nothing to do. the real solution relies on using 'the most excellent', DBD-AD0-2.83 (the current version) and look what we read in the *suprise* README file ....

$sth = $dbh->ado_open_schema( $QueryType, @Criteria ) or die ...;
the file continues ...

This method can be used to obtain database schema information from the provider. It returns a valid statement handle upon success.

the DBD::ADO perldoc online is here.

so what does this mean? It means that you can now find out the schema programatically for unknown Access databases. I found this by checking installing MSDN, searching on COM and IDBSchemaRowset. In an article, Data Access for the Masses Through OLE DB, José A. Blakeley, MSDN. You can get the catalog information through the IDBSchemaRowset interface.

In ADO the connection object supports this interface (OpenSchema). Behind this is a COM explanation. For those interested in this try reading OLE DB for the ODBC Programmer, Michael Pizzo and Jeff Cochran, MSDN.

This means you can query the "... types, tables, triggers, views etc ..." for any given Access database.

I should know all this stuff. The real credit goes to Tim Bunce and Phlip for good documentation. Oh, dont forget TMTOWTDI.

This is a fairly easy task if you are on win32. Use Win32 OLE to access it like so

sub getTableDefs
{
        $accessConst = Win32::OLE::Const->Load('Microsoft Access 8.0 O
+bject Library');
    $daoConst    = Win32::OLE::Const->Load('Microsoft DAO 3.51 Object 
+Library');

    #array to hold table names
    my @names;

    #Create a new OLE Access.Application object
    my $app = Win32::OLE->new('Access.Application');
    
    #Open the database being worked in
    $app->OpenCurrentDatabase($datasource);
        
    #Get the database object
    my $database = $app->CurrentDb();
    
    
    #Save every table name that is not a system or linked table
    foreach my $i (0..($database->TableDefs->{Count}-1))
    {
        #If the attributes are 0 (ie a normal table)
        if(!$database->TableDefs($i)->{Attributes} || $database->Table
+Defs($i)->{Attributes} eq $daoConst->{'dbAttachedTable'})
        {
            #add it to the list of valid selections
            push @names  $database->TableDefs($i)->{Name};
            
        }
    }
    
    #Quit the application
    $app->DoCmd->Quit($accessConst->{'acQuitSaveNone'});

    #Return the list of valid table names
    return @names;
}
[download]

This is some code I wrote a while ago. It does work but it might not be exactly what you want. It only counts non system tables and non-linked tables. this was in a package.. I moved the constant object declaration into the sub for clarity when I pasted it here.

Again.. this code has been tested and does work. I hope this can be of some use to you.

P.S. If you open up the object browser in access, and you can check what the possible values of the attribute method are, so you can know which table you want to count. There is also a QueryDef collection if you are interested in counting those.

Reverse Engineer MS Access/Jet Databases

The code that I have developed so far uses Win32::ODBC to do create/update a DSN and discover the driver to use, and names of the tables. DBI and DBD::ODBC are used to read each table in it's entirety and dump it to XML.

I much prefer the idea of using a fully DBI based solution to do both the schema detection work and the database reading. I will update my code and post it later today for critique and further analysis.
Thanks.

The code below lacks any real error handling and doesn't log any error/status information. I will be doing that later.

Be gentle with your comments!
Inman