Programmatically building an MS-Access DB

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

Replies are listed 'Best First'.
Re: Programmatically building an MS-Access DB by bmann (Priest) on Feb 22, 2005 at 23:36 UTC
Super search brought me to this: Re: Create a MS Access database programmatically in perl, which shows three ways to create the db and tables. Good luck!	[reply]
Re^2: Programmatically building an MS-Access DB by legato (Monk) on Feb 23, 2005 at 14:46 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`	[reply]
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.	[reply]
Re^2: Programmatically building an MS-Access DB by legato (Monk) on Feb 23, 2005 at 14:49 UTC
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`	[reply]
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;	[reply]
Re^2: Programmatically building an MS-Access DB by legato (Monk) on Feb 25, 2005 at 14:30 UTC
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`	[reply]

Back to Seekers of Perl Wisdom

use strict; use warnings; use diagnostics;