Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

DBI module and MS Access

by Anonymous Monk
on Oct 01, 2000 at 06:25 UTC ( [id://34799]=perlquestion: print w/replies, xml ) Need Help??

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

My boss wants me to write a script that'll enter data into a MS Access database. How can I do it? Can I use a DBI module? Is there any other module/way to do it. If not MS Access, he likes File maker pro. Thanks in advance

Replies are listed 'Best First'.
Re: DBI module and MS Access
by mattr (Curate) on Oct 01, 2000 at 11:00 UTC
    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.

      Access isn't quite that bleak but you aren't far off:P I think the hardest I have pushed a .mdb is 80 megs. Generating rollup reports for an 80 meg DB took about 15 minutes:P Although, at 10 megs it ate the reports for lunch. Keep in mind, I was never interfacing with the DB through the MSAccess application. I bet MS Access is not happy trying to refresh screens *shudder*.
      Access can also handle a fair number of connections at once. I can guess that I have had somewhere between 10 - 20 people on at once. I don't think I would trust it with much more though.
      I love your point about garbage! The first time I learned about that I was wondering where all my drive space was going when someone asked me when the last time I compacted my DB was.
        Uh oh...

        You use MS Access as a multi user DB? Have much trouble with data loss? It's a cool program for single user DB's, but I wouldn't trust it multiuser... Actually, it's possible that if you only use ODBC to access it things will be okay, but I've seen people share the MDB on a network drive and have multiple people opening the file. This is a good way to corrupt your database.

        Cheers!

Re: DBI module and MS Access
by puck (Scribe) on Oct 01, 2000 at 09:11 UTC
    Depends, if you're writing the Perl script under Unix you'll want to use DBI and DBD::ODBC with a suitable ODBC package (like OpenLink) to connect back to the Windows box. There currently isn't any feasible way to look at MS Access MDB files under Linux (I wish there were for a project I'm going to start work on soon, there is one tool I know of but it's very alpha...).

    DBD::ODBC can be used under Windows as well, I'd need to track down some code to check up on how to do it, but I think it might just work out of the box. Check the docs first though. (Anybody want to refute that?)

    Hmm if your boss wants to use Filemaker Pro, I'd suggest you check and see if there's an ODBC interface for it, I think there is, but I've never used it.

    Cheers!

Re: DBI module and MS Access
by 2501 (Pilgrim) on Oct 01, 2000 at 08:25 UTC
    Personally, I think you are on the right track. I would use DBI, because it means that later when your boss decides he wants to use Oracle or something else instead, You can use the code you wrote for the Access script for the Oracle project.

    Your other alternative could be Win32::ODBC, but why limit yourself?
P.S.
by mattr (Curate) on Oct 01, 2000 at 11:10 UTC
    By the way you might find Cygwin helps preserve your sanity if you are coming from Unix. You can get a bash shell on NT and compile things, it's great!

    Otherwise you may need to get nmake to build perl modules that need to be compiled.

RE: DBI module and MS Access
by little (Curate) on Oct 02, 2000 at 18:30 UTC
    Take a look at DBI::Proxy
    Though you can access MSAccess without problems of sharing the same file and without having to remit the whole file over your network.
    Have a nice day
    All decision is left to your taste

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (None)
    As of 2024-05-23 21:39 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found