Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Programmatically building an MS-Access DB

by legato (Monk)
on Feb 22, 2005 at 22:29 UTC ( #433511=perlquestion: print w/ replies, xml ) Need Help??
legato has asked for the wisdom of the Perl Monks concerning the following question:

It might be because I've had a long few weeks, or it might be because I simply haven't been able to come up with the right combination of search terms for Google and SuperSearch, but I simply can't seem to find a solution to my problem.

I have need to create and populate an MS-Access database on-the-fly from within a Perl-based CGI (under IIS/Win32). I have a large database stored in SQL Server, but I am being asked on a regular basis to provide the results from various queries as files to my users. Thus far, Excel (with Spreadsheet::WriteExcel) has been quite sufficient. However, there are now several queries that have returned 1.5e6 rows -- far more than Excel can deal with.

The queries vary considerably based on user input, so setting up a ODBC connection to a handful of databases and populating them isn't an answer -- I need to be able to create the files, and on a machine where I can't install Access and use OLE automation.

I've chosen Access because it is free to my end users (corporate-provided), and therefore ubiquitous. I am willing to use other formats that have "idiot-resistant" interfaces, so long as the clients don't require payment or admin rights to install. So, the only thing I'm aware of is Access. And, furhtermore, I don't have any troubles connecting to an existing DB file via ODBC; it's just that I can't find any way to create a new, blank Access database with an arbitrary name, then connect to it via ODBC.

Has anyone done this? Is there a module I haven't found on CPAN? Is there an existing script that I missed on Google, or a node about this I failed to find in the Monestary?

Thanks in advance for any help -- I just need a decent place to start.

Update 1: thanks to bmann for finding the PM node I was unable to, the solution there works (see comment by bmann below).

Update 2: periapt's solution works also. In fact, if the goal is merely to get data -- *ahem* acessible -- to Access, using dBase or XBase is a much faster, more elegant, cleaner solution. It also won't work for this application, but thanks to him anyhow. ;-)

Anima Legato
.oO all things connect through the motion of the mind

Comment on Programmatically building an MS-Access DB
Re: Programmatically building an MS-Access DB
by starchild (Sexton) on Feb 22, 2005 at 23:16 UTC
    One option might be to create an SQLite database (it's free and you don't need admin rights to install), and then for the GUI have an Access database that links to the SQLite db via an ODBC driver. I've had good luck with this at work.

    Unfortunately I've never tried to programmatically create an Access database, so I can't help there.

      Thanks for that idea. I'm using the node bmann suggested for this issue, but your suggestion has solved another issue I've been pondering for another project that I'm already using DBD::SQLite2 with.

      Anima Legato
      .oO all things connect through the motion of the mind

Re: Programmatically building an MS-Access DB
by bmann (Priest) on Feb 22, 2005 at 23:36 UTC

      See, now why couldn't I find that on SuperSearch? Need more sleep, I guess... ;-)

      In any case, I combined two of the methods from that node, and it works great! I use the DAO method (3) to build the DB file, and then connect via DBI (similar to method 1) to create/populate tables. Thanks!

      Anima Legato
      .oO all things connect through the motion of the mind

Re: Programmatically building an MS-Access DB
by periapt (Hermit) on Feb 23, 2005 at 16:36 UTC
    In the spirit of TIMTOWTDI, you could create a data set in a format that Access reads natively like dBase III. It is a bit more work but you get the added flexibility of having the output in a ubiquitous format that most programs, spreadsheets, databases, word processors and the like, can read easily. Corion has a description of the dBase header format at http://www.corion.net/cgi-bin/wiki.cgi/display/Format:DBase%20III. Once the header is built, the data is just appended sequentially with one byte added at the beginning for a deleted record flag.

    PJ
    use strict; use warnings; use diagnostics;

      That is an interesting thought. I had considered using the CPAN module XBase to generate dbf files, as I've had luck with that before. Unfortunately, the target audience for the delivery of these files is not tech-savvy, nor particularly bright. Think PHB (heh. Nearly typed 'PHP' ;P).

      The number one problem with generating dbf files (which are, incidentally, smaller than mdb) is that the users can't just click to open in thier browser or desktop. Yes, I could just say "open it with Access", but the last time I did something like that (TSV files and Excel) it caused me far too many headaches.

      Thanks for making the suggestion, though, as future querants will probably want to do that instead of bothering with the DAO annoyance I've got now. ++ from me!

      Anima Legato
      .oO all things connect through the motion of the mind

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (4)
As of 2014-08-01 02:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (256 votes), past polls