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.
In reply to Re: DBI module and MS Access
by mattr
in thread DBI module and MS Access
by Anonymous Monk
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |