Great question. I happen to think Perl, DBI, and MS Access make a wonderful set of front-end tools for any SQL Server shop (and an opportunity for evangelism).
You're right about your fallback option of bypassing Access and going straight to SQL Server with a big select statement. To mitigate the inconvenience, you can
- develop your query interactively in Access;
- convert what you have developed into a SQL pass-through query (thus getting instant feedback about whether your SQL code is back-end compatible); and finally
- paste your working pass-through query into your Perl program.
But to answer your question, yes, you should be able to modify your Access database so that DBI can connect to it programmatically. The key is simply to save your authentication credentials in the table links. Try recreating your links from the Access UI, and watch for the "Save password" check box in the Link Tables dialog box. It helps if you can save your ODBC connection in a File DSN.
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: |
| & | | & |
| < | | < |
| > | | > |
| [ | | [ |
| ] | | ] |
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.