I recently dove fearlessly into a project using Perl on
Windows NT / MS Access despite feeling comfortable in Linux.
It can be done, though I ended up using mysql the instant
I realized it had been compiled on NT, GPL'd, and offered
by mysql.com even if they don't exactly lead you to it.
I am very happy now and will install it in the next week or
so on a small office intranet. The system is not very big,
but 30 people will record hours spent on clients, adding up
to ten records a day each (my db uses one record per charge
so I can do tallying every which way). MS Access and Filemaker
are both relatively easy to get started on for the end user, but
the problem is that if you use it for something useful, you
very quickly hit performance limitations, for Access consider
that to be when your database goes over 10 MB. Micro$oft
leads you into buying SQLServer when that happens. Or, if
you have the Premiere version of Office (or developer studio,
or VB I think) you might be able to use the MLDB package which
I understand is like using the SQLServer engine on your Access
database.
Caveat, I have yet to get my linux box set up to be able to
access a Microsoft database via ODBC, since halfway through
trying to find and install the proper packages for iodbc
and openlink (SQLServer driver) I reached my own frustration
limit and went home Friday! On NT it is easier.
I had to surf a lot though to find perl ODBC libraries.
Try activestate's site, or check their cpan-like library
with the perl package manager (ppm.exe) command.
The Win32::ODBC module is also at
http://www.roth.net/perl/odbc/ which is one alternative.
Anyway, I believe I used DBD::ODBC to access the MS Access db file.
You will first need to register the .mdb file in your control
panel's ODBC manager to get a DSN (data source name), and (though I am definitely *not*
an NT guru) I believe you may want to register it under the
system db tab, not the file db tab. Anyway, once you finish
wandering through the manager you should end up with your
Access database being registered and you get a DSN which
you can use in your perl program or module to get a database
handle. The ODBC manager should be on your NT box (if it is
NT) already, unlike with Linux where it has to be added in.
You just need an ODBC interface from Perl, definitely try to
stay with DBI/DBD for delightfully easy future porting to
other databases.
I don't have the whole thing in front of me but this should
do it:
#!C:\Perl\bin\perl.exe
use DBI;
use DBD::ODBC;
my $DSN = 'perltest';
my $dbh = DBI->connect("dbi:ODBC:$DSN", '','')
or die "$DBI::errstr\n";
That said, I strongly recommend that you think up all the
possible reasons why you should use Mysql on NT instead of
MS Access. There is no reason why you should use a dumbed
down database engine. You may even end up having to rewrite
all of your database accesses if you use straight ODBC
commands instead of going through the DBI. I was able to
generate a report for six months of simulated data in about
ten seconds on a relatively cheesy NT box, and ordinary
operations were for all intents and purposes instantaneous.
Compare that to trying to add a single record to the middle
of a 20MB .mdb file in Access and waiting for the screen to
update (minutes for me). Also, I noticed that Access does
not automatically collect garbage, so the mdb file will remain
huge even after you have deleted a lot of records. And
Access probably cannot handle many simulataneous accesses.
The one problem I did find was probably due to a lack of
memory on the NT box (so get yourself some RAM). When
hitting the box with several simultaneous accesses, once in
a while I would get a command not found error (well it was
in Japanese..) at the top of the browser window, and only
when using IE5 as the browser (even when coming from another
box). The cgi would work but no data would show up in the
fields.. But no errors from mysql or Apache (also recommend
using Apache!). Looking at the running tasks I noted each
CGI process was about 8 MB, since I was using some big modules
(CGI.pm, Date::Manip, DBI) and after closing other apps
(especially IE and Explorer) I didn't have this problem any
more.
I have to get back to my machine to check next week
but I believe I registered Mysql with the ODBC driver and
am going through ODBC, but possibly the DBI::Mysql driver
may be a better way to do it in the future.
Sorry I don't have all the code to give you the definitive
answer. If it helps, I believe you can actually do an import
from across the network from an Access database into Mysql
in one shot, certainly you can use the import command to
load in a text file. My final recommendation would be
to use DBI::ODBC and Apache, try MS Access, and quickly move
to Mysql.
