Mike S wrote:
Anyone know how I can access a MS Access database from perl?
Any help appreciated.
A quick google
(
http://www.google.com/search?sourcei...soft+access%22)
found the following script at
http://hypernews.ngdc.noaa.gov/Hyper...16.html?nogifs
I dont know perl myself so I cannot attest to its validity.
<<script begins>>
use Win32::ODBC;
$DEBUG = 0;
$DriverType = "Microsoft Access Driver (*.mdb)";
$DSN = "Win32 ODBC --MAOmaoMAOmaoMAO--";
$Dir = "c:\\mydocu~1";
$DBase = "maotest.mdb";
# Change Field1 and Field2 of Record 1 (i.e., where first column of
table equals 1)
$primaryID = 1;
$newvalue[$primaryID] =
{(
"Field1" => "test Field1",
"Field2" => "test Field2",
)};
Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType,
("DSN=$DSN", "Description=MAO Win32 ODBC Test DSN for Perl",
"DBQ=$Dir\\$DBase", "DEFAULTDIR=$Dir", "UID=", "PWD="))
or die "ConfigDSN(): Could not add temporary DSN" .
Win32::ODBC::Error();
$db=new Win32::ODBC($DSN) or die "couldn't ODBC $DSN because ",
Win32::ODBC::Error(), "\n";
my(%data, $key) = $db->GetDSN();
foreach $key (keys %data) {
print "$key: <$data{$key}>\n";
}
# Get list of tables in database
@table = $db->TableList;
print "Tables: @table\n" if $DEBUG;
$query = "select * from $table[0]";
!$db->Sql($query) or die "couldn't do $query because ", $db->Error(), "\n";
# Get list of columns in first table of database
@column = $db->FieldNames();
print "Columns: @column\n" if $DEBUG;
# Check for typos in hard-coded column names in $newvalue above
for $thisvalue (@newvalue)
{
$J = ' ';
$searchin = $J.join($J, @column).$J;
for $name (keys %$thisvalue)
{
$searchfor = $J.$name.$J;
print "Looking for \"$searchfor\" in \"$searchin\"\n"
if $DEBUG;
$searchin =~ /$searchfor/ or die "Can't locate $name in
@column";
}
}
print "Before...\n";
&PrintDatabase($db);
$query = "update $table[0] set
$column[1]=\'$newvalue[$primaryID]{$column[1]}\',
$column[2]=\'$newvalue[$primaryID]{$column[2]}\' where
$column[0]=$primaryID";
!$db->Sql($query) or die "couldn't do $query because ", $db->Error(), "\n";
$query = "select * from $table[0]";
!$db->Sql($query) or die "couldn't do $query because ", $db->Error(), "\n";
print "After...\n";
&PrintDatabase($db);
Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $DriverType, "DSN=$DSN") or die
"ConfigDSN(): Could not remove temporary DSN because ",
Win32::ODBC::Error();
sub PrintDatabase
{
my $db = shift(@_);
my @table = $db->TableList;
my @column;
my $column;
my $query = "select * from $table[0]";
!$db->Sql($query) or die "couldn't do $query because ", $db->Error(), "\n";
@column = $db->FieldNames();
for $column (@column)
{
printf("%15.15s ", $column);
}
print "\n";
for $column (@column)
{
printf("%15.15s ",
"_________________________________________________ ______________________________");
}
print "\n";
while($db->FetchRow())
{
my %Data = $db->DataHash();
for $column (@column)
{
printf("%15.15s ", $Data{$column});
}
print "\n";
}
print "\n";
<<script ends>>
