×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Perl Database Resources

How do I update an Access Database Using Perl? by MikeLacey
Posted: 18 Jan 02 (Edited 5 Oct 04)

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:ODBC:DSN', '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();
}

Back to Perl FAQ Index
Back to Perl Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close