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

I know, yet another question about using MS Access. :) I promise to be brief. :)

Currently I have developed an application under Linux in perl that accesses a Progress database on HPUX using the OpenLink product and DBI:ODBC. This connectivity works great! OpenLink has a request broker that runs as a daemon on HPUX that you connect to from DBI:ODBC on the Linux box. The request broker then invokes a client program that contacts the Progress database. After this connection has been established, the SQL is executed.

<shameless plug> OpenLink is a great application </shameless plug>

My co-worker has been suitably impressed by this solution and has challenged me to connect Perl to a MS-Access database. Based on a little perlmonks.org research, this question is common. However, most of the questions are from people using Perl under NT. That solution seems very straightforward.

However, I'm wondering if any astute monks have been able to transfer an Access *.mdb file to a Linux box and connect to it through DBI:ODBC. If this has been done, how was the DSN specified to define the *.mdb file location? I've only used DSN's where the database connection has been established over a network connection. Is specifying a filename in the DSN even possible?

I can handle the configuration of the ODBC component but the DSN part is really confusing me.

Thanks in advance!

P.S. - here's a little tidbit about calling those predefined queries or database views that are stored in the MS Access database.

  • Comment on Perl to MS Access with DBI:ODBC on Unix

Replies are listed 'Best First'.
Re: Perl to MS Access with DBI:ODBC on Unix
by InfiniteSilence (Curate) on Dec 28, 2000 at 21:21 UTC
    I have heard this question asked before adn I figured that I would respond. You cannot effectively use an Access .mdb on any *NIX box. Luckily, you can (apparently) use ADO from a *NIX box to access an Access database, but moving the DB to *NIX apparently only offers 'access to tables' as listed by somebody who has apparently tried it here.

    I hope this helps.

    Celebrate Intellectual Diversity

[reply]

      I guess that depends upon your definition of effective.

      You must be defining dbi database proxying as ineffective, (It is a performance pig) but it does work.

      update -- ack! There I go reading the post wrong -- again!

      Putting the *mdb ON the *nix box... ack.. I guess my brain didn't want to accept the concept!

      coreolyn

[reply]
      Excellent! Thank you very much for your reply and off-site link.

      I'm not really an expert at the internals of Access, but it makes sense that the full features of the Jet engine cannot be used if the file is on the *nix box.

      Methinks a better option is going to be converting the Access database into a more friendly Database.

      Thanks again!

[reply]
Re: Perl to MS Access with DBI:ODBC on Unix
by coreolyn (Parson) on Dec 28, 2000 at 20:58 UTC

    It sounds like your not defining the ODBC data source on the NT side. The dsn="dbi:ODBC:$odbcDataSourceName" as defined in the NT ODBC manager.

    I should note however that the only way I was able to get it working was by resorting to the DBI::Proxy on the NT box and having the unix box talk to the proxy1.

    coreolyn

    1 See pg 179 of The Cheetah
[reply]
      As a followup to this, with standard disclaimer that I have not used OpenLink for this exact connection, I am going to run under the following assumption:

      You are using the OpenLink Request Broker on NT.

      If this is the case, I'm going to guess that you simply either make a connection to the Access database engine using the OpenLink software and specify the database in your DBI connect string.

      $dbh = DBI->connect('dbi:ODBC:server=$server;database=$mdbfile', $user, $pass, { RaiseError => 1});

      Thus, you're making your connection to Access and giving it the database file you wish to use. On the NT side, you may or may not need to provide a database specification in the OpenLink DSN.

      This is just a hazarded guess, but it would seem to be consistent with the DBI API, and from what I've seen of OpenLink (I use it for MS SQL 7 connections), it should be adequate, although I've only used DSNs with defined database connections. However, since each database engine has its own set of quirks, this may be the right way for Access.

      ALL HAIL BRAK!!!

[reply]
[d/l]
        Just out of curiosity, do you have any problems with memory leaks?
        My company's software and Openlink aren't playing nice, as there is a
        very LARGE memory leak being caused by Openlink (We confirmed that
        our software wasn't at fault, and Openlink said there was a bug with the
        drivers (Version 3.2). We also checked the 4.0 drivers, and still
        have the same problem.

        TStanley


        Update:We looked a little bit further into the problem and realized
        that our software did have the memory leak. Since Openlink
        doesn't play nice with us, we are now using Merant's DataDirect driver.
[reply]

Back to Seekers of Perl Wisdom