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

In reply to Programmatically building an MS-Access DB by legato

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.