Is there a way to refence the QueryDef name from the SQL it contains?

[mdlueck]

Someone brought up an intriguing question, so I thought to inquire among the peer group here:

Is there a way for a SQL QueryDef object in Access to look up the name of itself in the SQL and return that as a value in the result set?

Ex:

SELECT [foo],
       [bar],
       [something]
       @NameOfQueryDef
FROM [table]

 

[Pat Hartman]

No. You would need to run the query from VBA and your code could then pass in the query name as an argument.

 

[gemma-the-husky]

as long the syntax is EXACTLY the same you could search all the querydefs for the query

for each qdef in currentdb.querydefs
   if qdef.sql = teststr then
        msgbox(qdef.name)
   end if
next

 

[mdlueck]

This code appears to do what I was seeking...

Place this in a VBA Module:

Public Function dbutils_returnCallingQueryName() As String 
 
  Dim daoQdf As DAO.QueryDef 
  Dim strExecutingQuery As String 
 
  For Each daoQdf In CurrentDb.QueryDefs 
    If Left(daoQdf.Name, 1) <> "~" Then 
      If dbutils_IsLoaded(daoQdf.Name, acQuery) Then 
        strExecutingQuery = daoQdf.Name 
      End If 
    End If 
  Next daoQdf 
 
  dbutils_returnCallingQueryName = strExecutingQuery 
 
End Function 
 
Function dbutils_IsLoaded(strName As String, Optional lngType As AcObjectType = acForm) As Boolean 
 
  dbutils_IsLoaded = (SysCmd(acSysCmdGetObjectState, lngType, strName) <> 0) 
 
End Function

Place this in a QueryDef named "qryStrProdName":

SELECT ID, 
       strProdName, 
       strProdNum, 
       dbutils_returnCallingQueryName() AS QueryDefName 
FROM tblTestNullPNValues 
ORDER BY strProdName;

Execute the Query which produces the result set:

ID    strProdName    strProdNum    QueryDefName 
1    Test 1    555-1212    [COLOR=Blue][B]qryStrProdName [/B][/COLOR]
2    Test 2    123-4567    [COLOR=Blue][B]qryStrProdName [/B][/COLOR]
3    Test 3        [COLOR=Blue][B]qryStrProdName [/B][/COLOR]
4    Test 4        [COLOR=Blue][B]qryStrProdName[/B][/COLOR]