Getting SQL Statement From Saved Query in Microsoft Access Database

Jim has asked for the wisdom of the Perl Monks concerning the following question:

Suppose I have a saved query named qryUpdateSoupsOfTheDay in a Microsoft Access 2000 database named Menu.mdb. Suppose the SQL statement looks something like this:

UPDATE tblMenu
LEFT JOIN tblSoups ON tblMenu.Day = tblSoups.Day
SET tblMenu.SoupOfTheDay = tblSoups.Name;
[download]

How do I wrestle this SQL statement (i.e., the text "UPDATE ... tblSoups.Name;") out of the Microsoft Access database?

I would prefer to do it with DBD::ODBC, if possible -- but I suspect it's not possible. I suspect I'll have to use Win32::OLE, DBD::ADO or similar medieval torture, in which case I'm looking for some magnanimous fellow monk to spoon feed me a ready-made solution. If I have to spend a week reading MSDN to accomplish this trivial objective, I'll hurl myself off a tall building and then I'll never finish my project.

Pointers to existing resources and helpful nodes are greatly appreciated. I searched, but I did not find.

Thanks!

Jim

Comment on Getting SQL Statement From Saved Query in Microsoft Access Database Download Code

Replies are listed 'Best First'.
Re: Getting SQL Statement From Saved Query in Microsoft Access Database by pc88mxer (Vicar) on Jun 02, 2008 at 06:52 UTC
Here's some VB code to modify a stored query: Modifing a Stored Query with VBA Have a look at the `ModifyQuery` subroutine. It appears you can access the query's SQL using `cmd.CommandText`. That routine sets the `CommandText` property, but it seems reasonable that you should also be able to read it. This node: Re: Create a MS Access database programmatically in perl shows how to create the requisite `ADOX.Catalog` object in perl using `Win32::OLE`. Disclaimer: I'm not a VB or ADOX expert - I'm not even sure I know what I'm talking about :-)	[reply] [d/l] [select]
Re^2: Getting SQL Statement From Saved Query in Microsoft Access Database by Jim (Curate) on Jun 02, 2008 at 14:04 UTC
pc88mxer++! The property named "CommandText" is just what I needed to search the Web for ready-made solutions that will save me hours of reading ponderous and Byzantine Microsoft documentation on MSDN. Thanks for the helpful links! Jim	[reply]
Re: Getting SQL Statement From Saved Query in Microsoft Access Database by Gangabass (Vicar) on Jun 02, 2008 at 06:34 UTC
I'm sure you can do this via Win32::OLE. Here you can find some code to connect to you DB. All you need is to add code which works with AllQueries (may be i'm wrong) collection.	[reply]
Re: Getting SQL Statement From Saved Query in Microsoft Access Database by runrig (Abbot) on Jun 02, 2008 at 04:51 UTC
How much searching have you done, and what have you tried? Update: I missed the point of the question...move along, nothing to see here.	[reply]
Re^2: Getting SQL Statement From Saved Query in Microsoft Access Database by ikegami (Patriarch) on Jun 02, 2008 at 05:59 UTC
I don't see anything remotely similar to the OP's question in the first two pages of results returned by your query. Did you really find something useful?	[reply]
Re^2: Getting SQL Statement From Saved Query in Microsoft Access Database by Jim (Curate) on Jun 02, 2008 at 13:56 UTC
More magnanimous than that. I searched a lot. I hadn't tried anything yet because I didn't know where to start. Unfortunately, searching the World Wide Web for general terms such as "Microsoft" and "Access" didn't help me hone in on a useful answer to my specific question. Thanks. Jim	[reply]