Retrieve SQL string from indirectly addressed querydef?

[jhowley58]

Hi,

Given a parameter which contains the textual name of a defined Query , how can I extract the SQL string from it?

(I'm holding the name of the Query as a text string in a table. I want to pass the SQL string associated with the query into a form to act as a record source for a list box, and I need to parse the SQL to define the number of columns for the list box)

I hope that is clear. I wrote it and it sounds confusing to me. /

John

 

[jhowley58]

I just realised )

Just put the name of the parameter in the following construction:

SQL_String = currentDB.Querydefs(name_of_parameter).SQL

Thanks,

John

 

[FancyPrairie]

Try this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL as String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryYourQueryName&quot
strSQL = qdf.SQL

 

[jhowley58]

Thanks Fancy but I cracked it.. didn't my thing do the same as yours?

John.

 

[FancyPrairie]

Yes it does. You responded to your own question while I was typing my response. Therefore, didn't see yours until after I submitted mine.

 

[jhowley58]

HI Fancy,

It never has failed to surprise me how we get into a strait jacket at times. And then we ask the question. And somehow, just by asking it, we get a clearer pic of the problem..) I lose track of the times I have asked a colleague to look at a piece of my code to see where I went wrong and then suddenly saw the hand in front of my face!!.. )

JohnH.