|Just another Perl shrine|
Programmatically building an MS-Access DBby legato (Monk)
|on Feb 22, 2005 at 22:29 UTC||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 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. ;-)