Perl & Microsoft Access

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

I have written a simple but effective interface between Perl and MySQL using the information in the two excellent books by Paul Dubois MySQL and MySQL Cookbook. This boils down to:
1. making a connection to the database
2. Extracting data using SQL statements
3. Disconnection from the database.
Are there similar function for Microsoft’s Access?
I have looked in FAQs but could not readily see any reference to this problem.

Comment on Perl & Microsoft Access

Replies are listed 'Best First'.
Re: Perl & Microsoft Access by jZed (Prior) on Jan 13, 2005 at 18:47 UTC
Hopefully you wrote your programs using DBI and DBD::mysql. If you did, you should be able to switch the programs to use MS Access by using DBD::ODBC instead of DBD::mysql. See the DBD::ODBC docs for details of setting up a DSN for MS Access	[reply]
Re^2: Perl & Microsoft Access by merrymonk (Hermit) on Jan 14, 2005 at 08:07 UTC
I know that I used DBI since I have a ‘use DBI’ statement in my Perl module. However, I do not know if I have used DBD::mysql since I have made no reference to it. If it is of any help to solve this question my ‘connection’ statement is of the following form $dbh = DBI->connect($dsn, ‘user’, ‘password’) where $dsn includes a reference to ‘DBI:mysql:host, the host name and the data base name. If I am using the two facilities that you have referred to does it mean that it is as simple as replacing the ‘use DBI’ with ‘use DBD::ODBC’ and then making some changes to the connection call that reflect the need to connect to Access rather than MYSQL? I apprecaite that this is probably a very simple question but is there a web page that has the DBD::ODBC docs?	[reply]
Re^3: Perl & Microsoft Access by jZed (Prior) on Jan 14, 2005 at 14:46 UTC
You do not need to specifically "use" DBD::mysql, or DBD::ODBC, you are already using it correctly: use DBI, then refer to the driver in the connect() call and DBI will load the DBD for that driver automatically. And yes, changing ODBC for mysql is the main thing you will have to do. (I'm assuming you have DBD::ODBC installed, if not use ppm to get it or go here to download DBD::ODBC). You will probably need to set up a system DSN for your database or else google for DSN-less connection to create on-the-fly ODBC connections. Other needed changes may or may not be simple, depending on whether you used SQL that is specific to MySQL, if you're just doing simple connections and queries, you should be fine. The docs for DBD::ODBC (and all perl modules) are in the modules themselves, you can get to them with perldoc DBD::ODBC on the command line, or by Start Menu - Programs - ActivePerl - Documentaion, or by looking on CPAN, for example the DBD::ODBC documentation online.	[reply]
Re: Perl & Microsoft Access by CountZero (Bishop) on Jan 13, 2005 at 20:50 UTC
DBD::ADO is the way to go! (hey that rhymes) You can access Access as you would any other database for which exists a DBI/DBD module. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: Perl & Microsoft Access by holli (Abbot) on Jan 13, 2005 at 19:16 UTC
jZed already advised you about DBD::ODBC. however you can also use the native DAO- (Data Access Objects) or RDO- (Remote Data Objects) interface with the Win32::OLE-module. holli, regexed monk	[reply]
Re^2: Perl & Microsoft Access by jZed (Prior) on Jan 13, 2005 at 19:25 UTC
But, unless you have a specific reason for using Win32::OLE, this approach will be much less protable than using DBI and will not allow you to take advantage of the many add-on modules for DBI or the knowledge of the many monks here in the monastary who know DBI but who don't know Win32::OLE.	[reply]
Re^3: Perl & Microsoft Access by holli (Abbot) on Jan 13, 2005 at 19:34 UTC
that´s right. but if you are a windows programmer that has already used DAO/RDO-objects in other languages you might find that easier/more familiar. Also you can address the .mdb-files as files. You don´t have to set up an ODBC-connection for every database you wan´t to use. Acccess sees databases as files, and that is, IMHO, the most valuable feature of Access. You don´t have to struggle with tablespaces, usernames/passwords and other things that come with a DBMS like Oracle and the like. holli, regexed monk	[reply]
Re^4: Perl & Microsoft Access by jZed (Prior) on Jan 13, 2005 at 20:04 UTC

Back to Seekers of Perl Wisdom