How do I access the SQL behind a query using ADO 2

[SBendBuckeye]

In DAO, I can retrieve the SQL property of a QueryDef object to find out the SQL code behind the query. I would do something like this:

strSQL = CurrentDB.QueryDefs("QueryName&quot.SQL

How do I do that using ADO?

I have function that executes a Select query based on a parameter in one of 3 ways as below:

1. Parm string contains "Select " - Execute code directly

2. Parm is only one word - Check to see if it is a table name - If it is, SQL string = "Select * From " & parm - I can do this by checking if the name is in the AllTables collection

3. If neither case 1 nor 2 above then must be a query - I can verify that against AllQueries collection but cannot find where in ADO the query SQL string is stored.

Any ideas or suggestions are welcome! Have a great day!

 

[Tranman]

SBend,
Actually, you fetch the SQL in the same way, no matter which version of Access you're using (the QueryDefs Collection is part of Access, not part of DAO/ADO).

str = currentdb.querydefs("myQuery&quot.sql

In order to run the query (or a SQL string) with an ADO recordset, do the following:

rst.Open Parm,<activeconnection>,,,<commandtype>

If Parm is a SQL string, commandtype = adCmdText
If Parm is a querydef, commandtype = adCmdStoredProc
If Parm is a tablename, commandtype = adCmdTable


Here is a snippet that will run a SQL string, a Querydef, or return a Table:

Public Function ADORunQry(myStr)
Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection

con.Open &quot;Driver={Microsoft Access Driver (*.mdb)}; Dbq=&quot; & CurrentDb.Name & &quot;;&quot;

'Put logic to determine argument type here

rst.Open myStr, con, , , adCmdStoredProc 'run a querydef

rst.MoveFirst
'Whatever you want to do with the recordset...

rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Function


Using the Currentdb.Name avoids having to hard code the location of the database, in case you want to rename it or move it to a different drive or folder later.

So, all you really need to do is to use the same code and modify commandtype, based upon the argument you passed.

Hope that helps,
Tranman

 

[SBendBuckeye]

Thanks Tranman, that is exactly what I needed. I thought that was the case but when I tried to Dim a QueryDef or TableDef neither one displayed in Intellisense. This will force me to learn ADO and this is one of those little gotcha's that is part of the process.

Thanks also for the little tutorial on command types. I learned more from your response than I did with a couple hours digging around in help.

Have a great weekend and a well deserved star!

 

[Tranman]

SBend,
Glad I could help. There is something confusing me, though. There is no reason, if you wanted to have a querydef object, that you couldn't do something like:

Dim qdf as QueryDef
Dim myStr as String

Set qdf = currentdb.querydefs(&quot;myQuery&quot

myStr = qdf.SQL

I just didn't do it in my code because I didn't see any reason that you would need an object variable for the querydef. Probably I'm just getting spaced out as Miller Time approaches.......

Thanks for the star.

Tranman

 

[SBendBuckeye]

That is exactly what I would do all the time in Access97 with DAO.

Dim tdf As TableDef or Dim qdf As QueryDef

When I attempted to do the above in Access2000 with ADO I got a compile error. It allows me to set/retrieve properties etc per your suggestion but you can't dimension it, at least not that I have found. Very frustrating.

Have a great weekend!

 

[janeatwork]

Good stuff Tranman

A * 4 u

Jane