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

Hi I have just received 4,500 or so Access databases which I need to amalgamate into a single Access Database. Im used to using Perl DBI with Oracle and mySql so thought great just write a quick script to go through each connect to them query out the data and add to a new master database. However reading through the help here it appears I need to use DBD::ODBC which means I need to setup 4,500 ODBC connnections - yikes!!! Is there anyway to avoid this, opr is there a way to quickly create an ODBC connection, use it, delete it then move onto the next database and do the same thing? Thanks in advance

Replies are listed 'Best First'.
Re: Accessing lots of Access Databases
by Jenda (Abbot) on Jun 24, 2003 at 21:17 UTC

    No you don't :-) You may connect without a DNS. From the DBD::ODBC's docs:

    Connect without DSN
    The ability to connect without a full DSN is introduced in version 0.21. Example (using MS Access): 
    my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\cheese\\g$\\
+perltest.mdb'; 
my $dbh = DBI->connect("dbi:ODBC:$DSN", '','')
  or die "$DBI::errstr\n";

    [download]

    HTH, Jenda
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
       -- Rick Osborne

    Edit by castaway: Closed small tag in signature

[reply]
[d/l]
Re: Accessing lots of Access Databases
by Itatsumaki (Friar) on Jun 24, 2003 at 20:31 UTC

    Here is a sneaky way that takes advantage of the fact that there is one access DB per file: no more, no less.

    1. Get a list of your databases
    2. Copy the first DB to temp.mdb
    3. Create an ODBC to temp.mdb
    4. Process temp.mdb as needed
    5. Close the DB connect ($dbh->disconnect())
    6. Delete the DB (unlink(temp.mdb))
    7. Copy the next DB to temp.mdb
    8. Loop....

    Hope it helps!

    -Tats

[reply]
[d/l]
[select]
Re: Accessing lots of Access Databases
by Zero_Flop (Pilgrim) on Jun 25, 2003 at 03:18 UTC
    First, another option would be to use OLE. But probably the most easy solution would be to write a macro in the Access db that you what everything to be in. Under Macros look up the function transferDatabase. All you have to do is create a Dir lookup, then process each db with the "transferDatabase" function.

    Not very perlish, but then using the right tool for the job is very perlish.
[reply]

      Good idea, but if the databases to be transferred are large, you may quickly hit the limits of Access' database engine. This will depend on the version you are running, so it pays to check this beforehand.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
        True, you always have to make sure your tools can handle the load. In this case this person wants to move 4,500 db into one. This makes me think that the size of each of the 4,500 db is actually small. If not then he will probably hit the 2G limit of the db. The quickest test would be to see how big the folder is that is holding the dbs. Each db will have some additional overhead that will be gained during the move, but if the folder is below 2G he should be good to go.

        As a quick check I generated a empty db. It took up 100k so in its current state he is losing at least.450,000K of space.

        Enough of THIS, let's get back to Perl!!!
[reply]
Re: Accessing lots of Access Databases
by NetWallah (Canon) on Jun 25, 2003 at 00:45 UTC
    Jenda's previous post has the right idea, but I'd like to correct the terminology.

    That is DSN, not "DNS", and I would name the variable "$connectionstring", instead of $DSN.

[reply]

Back to Seekers of Perl Wisdom