Re: DBI, ODBC, Linked table and authentication

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.

Comment on Re: DBI, ODBC, Linked table and authentication

Replies are listed 'Best First'.
Re^2: DBI, ODBC, Linked table and authentication by iwik (Initiate) on Apr 03, 2008 at 09:59 UTC
Thanks, I've pretty much got it running along the lines of your suggestion; I had a good peek at the SQL Access was generating and munged that into the Perl code. Because the query was itself nested I had to flatten that first... It's a tad uglier than I wanted (in its own way SQL can end up as unreadable as a good regex), but at least its doing what I want. I did have a peek earlier to try and see if there was something in the links that would allow credentials to be defined there, but couldn't find anything. However, I didn't go as far as recreating links, and its always easier to look for something that you know exists, than for a possibility that might not. I'll go back and try that path - I never liked having to supply the credentials in any case, so embedding them in the links would be a plus all round. Thanks for the pointers Andrew	[reply]
Re^3: DBI, ODBC, Linked table and authentication by iwik (Initiate) on Apr 07, 2008 at 21:17 UTC
100% right. I've just got around to having a play, and when setting up the Access link, you have the option of saving the password, which embeds the access credentials in the link properties. There does not appear to be any option of modifying an existing link to do this. So the answer was in Access. This gives me a solution that will work perfectly, and solves another problem at the same time. Thanks for the pointer. Andrew	[reply]