How to link Ms-access data's in perl

[Anonymous Monk]

Hi monks,

I have a database in ms-access. Inspite of getting input from text file i having the data's in database. so, please help me the code, how to get the data's from ms-acess to perl.
Thanks in advance.

[monkfan]

Hi,
Try this.

#Windows-based Perl/DBI/MS Access example
 use DBI;

 #open connection to Access database
 $dbh = DBI->connect('dbi:ODBC:Clients');

 #prepare and execute SQL statement
 #note that the query ClientName etc below is just the example
 # suit it with your own requirement
 
 $sqlstatement="SELECT ClientName,ClientEmail FROM billing";
 $sth = $dbh->prepare($sqlstatement);
 $sth->execute || 
       die "Could not execute SQL statement ... maybe invalid?";

 #output database results
 while (@row=$sth->fetchrow_array)
  { print "@row\n" }

[ikegami]

ADO supposedly works great:

my $db_file   = 'd:\path\to\database.mdb';
my $db_user   = 'username';
my $db_passwd = 'pasword';

my $dbh = DBI->connect(
   'dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source='.$db_file,
   $db_user,
   $db_passwd
) or die $DBI::errstr;
...

It bypasses ODBC which ends up connecting to the Jet Engine anyway. It also saves you from creating ODBC DSNs in the Control Panel. (yeah, I suppose you could use DSN-less ODBC connection, but people don't seem to do that for some reason.)

Update: oops, this was supposed to be a reply to the OP.

[gube]

Hi try this,

Before doing this install DBD-ODBC module in your system. Add database in ODBC before run this perl code

use DBI;

# MAPDSN is database name
 $dbh = DBI->connect('dbi:ODBC:MAPDSN');

#pii is table name
 
 $access="SELECT aid,piino FROM pii";
 $sth = $dbh->prepare($access);
 $sth->execute();

 while (@row=$sth->fetchrow_array)
  {
       $pii = $row[0];
       $pii1 = $row[1];      
       $pii{$pii}=$pii1;
       
  }


Regards,
Gube.

[simon.proctor]

If you are dealing with Memo fields from Access then you may find it best to create ADO objects directly. Here are nodes I wrote on these sometime in the distant past. They should provide a start point if you need it:
• Retrieving the last insert ID with Access
• Re: Cannot insert into MS Access Memo field with DBI
The above will show you how to connect, query and, if required, grab the insertion id of a new record if your logic requires it.

HTH

[Robertn]

In the interest of completeness, you might also wish to look into using Win32::odbc. You can find more information here: http://www.roth.net/perl/odbc/faq/

[richardX]

One tip for making large strings and memo fields work better with Access is to use the following code:

my $dbh = DBI->connect( "dbi:ODBC:home", "", "",
    {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or
die "Unable to connect: " . $DBI::errstr . "\n";


$dbh->{LongReadLen} = 20000 ; # to better handle long variables

[bfdi533]

These solutions all seem to point to running and accessing the DB file on a Windows system.

Is there a solution for Linux PERL can use to read/write MS Access DB files?