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

I am rather new at perl. My experience is with mysql database which I like however, I have an application I must write using an existing MS Access Database. I have a few questions. I am use to the sytax of my $dbh = DBI->connect('dbi:mysql:database=datasite','',''); I have noticed there are several ways to do it. 1.What is the best way to connect to ms access? 2.what is the sytax? An example for updating and inserting would be great. The easier the better. I will be updating, deleting, inserting, searching etc... 3.What perl modules do I need to install for 5.6 build 633?

Replies are listed 'Best First'.
Re: Access Database
by jasonk (Parson) on Feb 27, 2003 at 16:04 UTC

    You can access Access databases, using the DBD::ODBC driver for DBI. There are plenty of examples in the documentation, but other than the DSN you pass to DBI->connect, it all works just like DBI with MySQL does.

[reply]
      Thanks for the quick response. I am sorry to be ignorant but where do I find the documentation to get examples of the syntax for ms access?
[reply]
Re: Access Database
by tachyon (Chancellor) on Feb 27, 2003 at 16:37 UTC

    It is a two step process. First register your database with windows ( ie create a DSN ): 

    Start|run|odbcad32
System DSN tab
Add
In create new datasource select Microsoft Access Driver
Click Finish
Now select your database and give it a name like 'mymdb'
OK away

    [download]

    This registers you Access MDB with the correct driver and the name 'mymdb'. You now connect like this and proceed as usual 

      $dbh = DBI->connect("DBI:ODBC:mymdb" ....blah

    [download]

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]
[select]
      OK, I do a query in ppm and dbd-odbc shows up. Is this the right module?
[reply]
        # yes
C:\>ppm install dbd-odbc

        [download]

        cheers

        tachyon

        s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]
Re: Access Database
by meetraz (Hermit) on Feb 27, 2003 at 22:08 UTC
    You may also want to look at DBD::ADO. ODBC is nice for general connectivity, but there is a big speed penalty for using it. I think DBD::ADO would give you better performance.

    Update: Apparently, DBD::ADO is even slower than ODBC. I benchmarked selecting 5000 rows using selectall_hashref() and DBD::ODBC is about 25x faster than DBD::ADO. But, I also had to rewrite my query into a format that would be accepted by ODBC. DBD::ADO will accept a straight Access Query, while ODBC will not.

    Also, DBD::ODBC requires you to manually set up an ODBC DSN before hand, while with DBD::ADO you do not.

[reply]

Back to Seekers of Perl Wisdom