How to connect Perl to MS Access?

Dear Experts, please help.

I'm using windows 2000, I have a MS Access database, and I just install ActivePerl 5.8.8.
What I want to do is use Perl to get a query from MS Access, and because the query is for managers only, so they need to type a username and password to connect, all the managers can use same username and password.

It seems I have to install win32::ODBC first. But the link http://www.roth.net/odbc/odbc.html is not working anymore.

Can someone please show me setp by step in detail for how to do this?

Thank you


kiiroweiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

netfunkCommented:
Go the http://ppm.activestate.com/

Choose your version .. after that, it would show you two (tabbed) options at top:

Build and zip. Choose the zip option, scroll down to win32::ODBC zip and download it. Upon unzipping, you would find a read me file, a PPD file and various OS specific *.pm files. Choose your OS specific .pm file, PPD and read me file and extract it into your lib folder in Perl. Then you can use these packages to suit your needs.

You can refer to CPAN for further help.

Cheers.
0
Adam314Commented:
This page has a very good tutorial:
http://www.databasejournal.com/features/msaccess/article.php/10895_1408481_1

In summary:
Use ppm (perl package manager) to install the DBI module and the DBD-ODBC module
At command prompt, type "ppm"
type "install DBI"
type "install DBD-ODBC"


And the perl code
use DBI;

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

#prepare and execute SQL statement
$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"
}


As for the username/password part: You'll probably get better responses in the web TA, but here goes:
store username and encrypted password in DB.  Also store what permissions a user has in DB.
when someone supplies username and password, encrypt password and check username/password against db
If they match, create a session - store users IP address and username in session vars
When someone goes to a secure page, check for session.  If there isn't one, send them to logon page.  If there is one, check that users IP match session IP.  If they don't send to logon (or access denied).  If they match, check username permissions DB to see if that user has permission to look at what they are trying to look at - if so, show them.  If not, display access denied.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

netfunkCommented:
Apologies for the previous post, I mistook your requirement for package installation.

Go to http://aspn.activestate.com/ASPN/CodeDoc/libwin32/ODBC/ODBC.html and you will the activestate documentation.

Activate the PPM(Perl Package Manager), and give the command "install Win32-ODBC", followed by "y" and it should install for you. You would also need to "install DBI" and "install DBD-ODBC"

More help can be found at:
http://www.sqlservercentral.com/columnists/bkelley/connectingtosqlserverusingperlwin32odbc.asp
http://www.roth.net/perl/odbc/faq/
http://www.lins.fju.edu.tw/~tseng/courses/dbms/faq.html

cheers!
0
skacoreCommented:
the Active State perl package comes with Win32-ODBC installed.  To access the database, you need to configure Windows with an ODBC to the database, then reference that throughout your script.

the following opens a connection and runs a command.  

<snip>
#!perl

use Win32::ODBC;
use strict;
use warnings;

sub run_sql
{
      my $request = shift;
      my $dbh = new Win32::ODBC("DSN=MyAccessDatabase;");
  #warn $request . "\n";
      if ( $dbh->Sql($request) )
      {
          #warn "SQL failed on request $request.\n";
          warn "Error: " . $dbh->Error() . "\n";
          $dbh->Close();
          exit;
      }
      return $dbh;            
}
1;

</snip>

for instance, I'll do

my $wo_query = "SELECT Work_Order_Nbr, Description FROM (Work_Orders INNER JOIN Customers ON Work_Orders.Customer_Nbr = Customers.Customer_Nbr) INNER JOIN Funding_Types ON Work_Orders.FundingType=Funding_Types.Funding_Code ORDER BY Work_Order_Nbr";
my $wo_get = run_sql($wo_query);
while($wo_get->FetchRow())
{
   my $cur = $wo_get->Data("Work_Order_Nbr");
   my $wo_desc = $wo_get->Data("Description");
   print "Current Order = $cur\nDescription= $wo_desc\n\n";
}

I have an ODBC created in windows named MyAccessDatabase that points to an access file.  There is a GUI to make this in Windows Administrative Tools, make sure its pointed to the actual .MDB access database.
That should be all you need.  You don't need to download an additional modules if you're using Active State activeperl.  DBD-ODBC is not required nor is DBI if you use the Win32-ODBC module and commands.  DBI and DBD-ODBC are more flexible and might offer more features, but Win32-ODBC is sufficient for connecting, INSERT, UPDATE, SELECT, and DELETE commands.
0
Adam314Commented:
The Win32-ODBC module, as skacore points out, will work.  If you ever want to be able to use a different database (database outgrows access...), and you use DBI/DBD, you can use the exact same code (different connection string).  Using Win32-ODBC, you'd have to make changes.  Downloading and installing a module (esp when activestate has already built it) is extremly easy though.  

Either way, if you need more info, post any questions you have.
0
kiiroweiAuthor Commented:
Experts, thank you for all you help.

Here is what I did, I intsall the DBI and DBD-ODBC.
Add my Access file to the ODBC, DSN is "Clients".
Then I use this perl code below, I save it as test.pl, but when I double click on it, it didn't return anything, it just open a command prompt then close it self. Is there anything I missed? how can I see what it returns? and how can I show the return in a web page? Thank you.

use DBI;

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

#prepare and execute SQL statement
$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"
}
0
Adam314Commented:
It opened a command window, executed the code, displayed it's results, and closed the window - all so quick you didn't see the results.

The easiest way to run this is go to a command prompt (start -> programs -> accessories -> command prompt)
Go to the directory that has your file
    so if your file is in c:\documents and settings\kiirowei\My Documents
    type (include the quotes):
         cd /d "c:\Documents and settings\kiirowei\My Documents"
Type the name of your perl program
    so if your program is called testdb.pl, type:
        testdb.pl


This will run your program, and you will see the results.
To show the results in a web page, you need to setup a webserver.  Do you have one?
0
kiiroweiAuthor Commented:
Adam:
Thank you for your help.

I run the test.pl and I got this message:

C:\Program Files\Apache Group\Apache2\cgi-bin>test.pl
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1. (SQL-07002)(DBD: st_execute/SQLExecute err=-1) at C:\Pro
gram Files\Apache Group\Apache2\cgi-bin\test.pl line 10.
Could not execute SQL statement ... maybe invalid? at C:\Program Files\Apache Gr
oup\Apache2\cgi-bin\test.pl line 10.

Yes, I have apache as webserver. That's why I'm tring to find out how to do this.
My ultimate goal is use Perl to get query from Access then post the query to HTML page.


0
Adam314Commented:
Try this instead for the line 10.  It should print a better error string.
$sth->execute || die "Could not execute SQL statement:" . $dbh->errstr . "\n";


For the websever, there is a perl module to help create a webpage:
http://search.cpan.org/~lds/CGI.pm-3.20/CGI.pm
The way it'll work is that any standard output from your program will be sent to the web client.  So you just need to make your program output the way you'd want the HTML to look.
0
kiiroweiAuthor Commented:
Adam, Thank you for your help. I think I need to try more for my self. I will post other questions if I need. Once again, Thank you so much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.