MS Access and Perl?

[Pic]

I've got some data in an Access database that I'd like to extract and put in a PostgreSQL database. What I don't have is access to Access.

Is there a way I can connect to an Access .mdb from Perl on UNIX (or failing that, using ActivePerl)?

Thanks for any input you've got.

[GrandFather]

DBI talks happily to Access databases.

[samtregar]

It does? What DBD would you use? Naive searchs on CPAN aren't pulling anything up. Seems like DBD::ADO might work, but it's not exactly obvious.

-sam

[GrandFather]

In the past I've used code that starts:

use strict;
use warnings;
use DBI;

my $db_file   = 'C:\...\database.edb';
my $dbh = DBI->connect
  ('dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' . $db_file)
  or die $DBI::errstr;

'database.edb' is an access database file. I don't recall any problem getting it to go, but it was some time ago now.

[BigRare]

The only way I am aware of converting from .mdb is by using ODBC from Windows.

Modules you may want to check are:
DBD::ODBC
Class::DBI::MSAccess

Otherwise, if the data could be converted/exported to CSV, this would be a much easier conversion.

[gellyfish]

On a Unix platform you may be able to use the mdbtools odbc driver alongside the unixODBC and DBD::ODBC. I have succesfully built and played with this setup but I can't say I have ever used it for anything serious.

/J\

[socketdave]

You don't need Access to set up an ODBC DSN to a .mdb file. A plain Windows (at least Windows 2000) install is enough.

Set up the DSN then use DBD::ODBC. At that point you can either use Activestate on the Windows box to connect to PostgreSQL on the remote machine or you can use DBI::ProxyServer and DBD::Proxy to connect from the *nix box back to the Windows box.

The former will probably require a little less work. I'd use the later if I needed ongoing access to the data in the .mdb and I wanted to keep my Perl all on the *nix box.

Update: To be clear, Activestate on the Windows machine is needed for either of these solutions...

[rhesa]

You don't even need to set up a DSN to talk to Access databases, you can simply pass the file name of the .mdb file:

$dbh = DBI->connect(q{dbi:ODBC:driver=Microsoft Access Driver (*.mdb);
+dbq=\\fooserver\path\source.mdb}, '', '');