Re: Flattening Access DB to XML

[g00n]

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.

method 2
last ditch effort (requires linux box) you could try mbdtool to tackle step 4. here's a brief description of how to generate the schema of a ms access file. (from cvs) It's license is LGPL but may have limited availablity on win32 (base install requires glib2).

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

method 3
read the discussion on Microsoft access database on a Linux server from php ('success on mdb tools odbc install' post by lauram) using the MDBTools ODBC driver. Any reason this can't be done using perl?

[g00n]

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.