http://www.perlmonks.org?node_id=2626

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How can I talk to an Access database using perl?
by buzzcutbuddha (Chaplain) on Apr 14, 2000 at 16:54 UTC
    The best way to talk to an Access database is through
    ADO (ActiveX Data Objects). If you have ever used
    ADO in VB or ASP, then you already really know how to
    use it!
    use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; my $conn = Win32::OLE->new('ADODB.Connection'); my $rs = Win32::OLE->new('ADODB.Recordset'); $conn->open('MIS'); # this opens an existing DSN connection $rs = $conn->execute('Select * From BackUpLog Order By DateTimeSta +mp;'); $rs->MoveFirst; while (!$rs->eof) { my $DTStamp = $rs->Fields('DateTimeStamp')->value; print "The DateTimeStamp is $DTStamp!\n" if ($DTStamp); $rs->MoveNext; } # trap any errors with the Win32::OLE->LastError() object print "That didn't go so well: ", Win32::OLE->LastError(), "\n" if + (Win32::OLE->LastError()); $rs->close if ($rs); # always make sure you close both the records +et $conn->close if ($conn); # and the connection itself

    That should give you the idea. I have used it for several projects and it
    honestly seems as fast as the 'Native' use in ASP. hth, Maurice
Re: How can I talk to an Access database using perl?
by Anonymous Monk on May 03, 2000 at 22:40 UTC
    Win32::ODBC comes bundled w/ the newer builds of ActiveState Perl (http://www.activestate.com). Define your Access DB as an ODBC source in the control panel, then call it as such. Example:
    $SQLcall = "SELECT * FROM main WHERE main.whatever = '$whatever'"; use Win32::ODBC; $DB = new Win32::ODBC("TheNameYouGaveYourODBCSource"); $DB->Sql("$SQLcall"); $DB->FetchRow(); $check = $DB->Data(); $getwhatever = $DB->Data("whateverfieldnameyouwant +"); $DB->Close();
    FAQ available at http://www.roth.net/odbc/odbcfaw.htm. Luck.
Re: How can I talk to an Access database using perl?
by btrott (Parson) on Apr 14, 2000 at 06:31 UTC
    You can use DBD::ODBC, which is a database driver for DBI:
    my $dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');
    And, I presume, you just use $dbh just as you would for any other database driver.

    Or you could use Win32::ODBC:

    my $Data = new Win32::ODBC("MyDSN"); $Data->Sql(SQL);
Re: How can I talk to an Access database using perl?
by buzzcutbuddha (Chaplain) on Apr 14, 2000 at 19:13 UTC
    I forgot to mention that the good thing about ADO is the fact that it allows you
    to use the same methods throughout all of your DNS entries, regardless of the
    source. So the same code should work for a SQL Server table as it would for a
    Paradox table, or your Access tables. I've tried to use the ODBC module included
    with ActivePerl, but it did not seem as clear as the ADO method.
Re: How can I talk to an Access database using perl?
by Anonymous Monk on Mar 19, 2001 at 23:28 UTC
    I tried the last piece of code here and it worked in command line fine. Here´s what I got when I simply tried to access the same script from web and print $getwhatetever in html. Server that I´m using is nt2000 and I think everything should be fine. System DSN - ODBC-driver ...is there simple explanation for this not so simple question? :) CGI Error The specified CGI application misbehaved by not returning a complete set of HTTP headers. The headers it did return are: Can't call method "Sql" on an undefined value at C:\Inetpub\wwwroot\cgi-bin\tietokanta\tietokanta.pl line 8.
      Problem solved! All of those who work with Access database and win2k server, remember to make driver(?) for both: User DSN and System DSN!!! Esa