Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Accessing Access DB with DBI

by skyler (Beadle)
on Jun 03, 2004 at 20:42 UTC ( #360481=perlquestion: print w/replies, xml ) Need Help??

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

Hello Monks, I'm very familar with DBI. I would like to write a script that would get into MSAccess select certain data out of a table and store it into array variables. then access SQL Server 2000 into a DB and grab another set of data. Finally combine the data into a pipe(|) delimited report. I have DBA background. I just need to know how to connect successfully to MSAccess & SQL Server 2000 to grab the data that I need to create the report. Could you point me in the right direction to look for information about it? or if you have a snippet that I may be able to use to accomplish this task as well. I appreciate your help. Thanks!

Replies are listed 'Best First'.
Re: Accessing Access DB with DBI
by Rhose (Priest) on Jun 03, 2004 at 20:52 UTC
    Here is some code I have for connecting to Access:

    $DSN = 'dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq='.$Databas +e; $DBH = DBI->connect($DSN, undef, undef) || ExitProg(ERR->{odbc},'['.$DBI::errstr.' - '.$DBI::err.']'); $DBH->{LongReadLen}=2048;

    And here is some code I have for connecting to MS SQL Server:

    $DSN = 'dbi:ODBC:driver={SQL Server};server={'.$Server.'};database={'. +$Database.'}'; $DBH = DBI->connect($DSN, undef, undef) || ExitProg(ERR->{odbc},'['.$DBI::errstr.' - '.$DBI::err.']');

    Please note... ExitProg is a sub I use, and is probably not in your program. *Smiles* Also, you will need to fill in the blanks for $Database and $Server as well as set your own value for $DBH->{LongReadLen} (if you use "memo" fields).

    One more also... I have "undef, undef" for username and password... if you need those, they are important to fill in. *Grins* (The example snippet was from a program which used Windows authentication to the database.)

Re: Accessing Access DB with DBI
by xorl (Deacon) on Jun 03, 2004 at 20:51 UTC
    It's been a while since I had to connect to an MS Access database. When I did I used the ODBC drivers. You have to have them installed on the box with MS Access and you have to use DBD::ODBC I haven't had to connect to SQL Server 2000 but I think it also uses ODBC.
Re: Accessing Access DB with DBI
by punkish (Priest) on Jun 03, 2004 at 21:23 UTC
    Could you point me in the right direction to look for information about... how to connect successfully to MSAccess & SQL Server 2000
    Use DBD::ODBC. It works beautifully for both Access and SQL Server. I am using it right now for a program. Not a problem at all.
Re: Accessing Access DB with DBI
by WhiteBird (Hermit) on Jun 04, 2004 at 03:13 UTC
    I have separate scripts that tap into both Access databases and an MS SQL server. I imagine that it would be easy enough to combine both calls into one script, get the data you need and do what you want.

    I use Win32::ODBC for my connection to the MS SQL Server. I like the syntax that it uses to call stored procedures. A sample connection into the database would be something like:

    my $db = new Win32::ODBC( "DSN=INFO;UID=id;PWD=password" ); if( ! $db ) { die "Error connecting: " . Win32::ODBC::Error() . "\n"; }

    For my Access database connections I use dbi::ODBC and the call looks like:

    my $dbh = DBI->connect("dbi:ODBC:INFO","username","password") or die "Unable to connect: " . $DBI::errstr . "\n";

    Note the differences in the way the username and passwords are called in each case. You can either hard code the username and password into the call, or you can place them in a file out of the document root and refer to the file in your call. The "INFO" in each call points to the DSN that is set up through the Windows Admin tools on the server to connect to the database. I'm assuming if you have a DBA background, then you know how to do that.

    Most of the information that I needed to understand how to use DBI and Win32::ODBC came from the documentation for the modules and from web searches. Dave Roth has helpful information on Win32::ODBC.

    Hope this helps.

Re: Accessing Access DB with DBI
by dba (Monk) on Jun 04, 2004 at 13:23 UTC
    You could simply do your work in SQL Server 2000 itself as a DTS package. Have two data sources configured - one for MS access and other for SQL server, 'transform' the data and output to the format you want. Good Luck...

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://360481]
Approved by Happy-the-monk
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2021-10-19 10:13 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (76 votes). Check out past polls.