How do I update an Access Database Using Perl?

Please browse through faq219-2884 and faq219-2889 first. Comments on this FAQ and the General FAQ's are very welcome.

If you're running Perl on a Windows machine this can be done quite easily.

You can maintain Access tables from Perl using the DBI, the DBI allows you to maintain many kinds of database.

If you're running ActiveState Perl on your Windows machine, and you probably are, you should already have DBI installed, this one line program will work if it's installed.

use DBI;

Next you need to install DBD::ODBC which is the ODBC driver for the DBI.

You can do that by typing the following command in a DOS window with your Perl Bin diretory as your current directory. Make sure you're connected to the Internet first.

ppm
install DBD::ODBC
quit

If it turns out you don't have the DBI installed you can install it in the same way.

Next you need to set up a ODBC DSN to connect to your Access database. You can do this in Control Panel.

Then review the DBD::ODBC documentation for instructions on how connect to a DSN from using Perl.

Once this is done you should be able to insert rows into an Access table like this: (Assuming that the table TEST_TABLE with two fields FIELD_A and FIELD_B already exists)

use DBI;

# connect to your DSN
$dbh = DBI->connect('dbi:ODBCSN', 'user', 'password');;

$sql = 'insert into TEST_TABLE values ('a', 'b');

$rv = $dbh->do($statement);

$dbh->commit();
$dbh->disconnect();

This is the simplest way to insert a single row into a table, for most databases (though not Access I suspect) it's more efficient to do it like this if you're inserting many rows, the code below would replace the $dbh->do line above:

# assumes each line of file.txt
# contains a row separated by a :
$sql = 'insert into TEST_TABLE values (?, ?);
$sh = $dbh->prepare($sql) or die $DBI::errstr;
open(FILE,'file.txt') || die;
while(<FILE>)
($var1, $var2) = split(/:/);
$sh->execute($var1, $var2);
$dbh->commit();
}