How to connect Access2007 by DBI in Perl

 

#The example runs on Windows platform.

#Before using DB, make sure DBI and DBD::ODBC packages are installed in advance.

#See PPM command in perl

#For more details, take perldoc DBI as reference.

#Note that the driver for Access2007 is a little different with previous versions.

# The DB suffix is *.accdb instead of *.mdb.

#If DBI and DBD-ODBC have not been installed in the system, first make sure the two modules been installed correctly.

# Run the command "ppm install DBI" and "ppm install DBD-ODBC" respectively.

#If you are behind the firewall, proxy server must be set so as to connect to Internet successfully.  set http_proxy=http://proxy.com/, then run "ppm install...".

#For more detail, type perldoc DBI as reference

use DBI;

 

my $dsn = "Provider=Microsoft.ACE.OLEDB.12.0;DSN=TestDSN";

#If is .mdb file. my $dsn="Provider=Microsoft.jet.OLEDB.4.0;DSN=TestDSN";

# my $dsn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C://TestDB.accdb";

my $username = "";

my $password = "";

my $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $password) or die "Can not open the DB: $DBI::errstr";

 

my $sth = $dbh->prepare("select * from TABLE");

my $rv = $sth->execute or die "Can not execute the statement";

my $rec;

while($rec = $sth->fetchrow_arrayref)

{

    print "$rec->[0], $rec->[1]/n";

}

#my @rec

# while(@rec = $sth->fetchrow_array)

#{

#   print "@rec/n";

#}

#

$dbh->disconnect;