SQL Query in tables 1

[vburrows]

I am trying to figure out how I would go about using vba to pull all the queries out of a db and in to a memo field in access table. Basically I would like to be able to use a queries to iniitate a query.

 

[MajP]

Maybe

Public Sub insertQueryNames()
  Dim aQueryNames() As String
  Dim varName As Variant
  Dim strSql As String
  aQueryNames = Split(getQueryNames, ";")
  DoCmd.SetWarnings (False)
  For Each varName In aQueryNames
    strSql = "INSERT INTO tblQryNames ( strQryName ) SELECT '" & varName & "' As strQryName"
    'Debug.Print strSql
    DoCmd.RunSQL strSql
  Next varName
  DoCmd.SetWarnings (True)
End Sub

Public Function getQueryNames() As String
  Dim strNames As String
  Dim qryDef As DAO.QueryDef
  For Each qryDef In CurrentDb.QueryDefs
    'Debug.Print qryDef.Name
    If Not Left(qryDef.Name, 1) = "~" Then
      strNames = strNames & qryDef.Name & ";"
    End If
  Next qryDef
  If Len(strNames) > 0 Then
    strNames = Left(strNames, Len(strNames) - 1)
  End If
  getQueryNames = strNames
End Function

 

[MajP]

you can also incorporate some of the ideas from your other post about controls on a form.
Use the MSysObjects table where Type = 5 (as AceMan shows) instead of the querydef
Create and clear the table as Remou shows.

 

[vburrows]

thanks and That is great now is there a way that I can pull the actual query (the sql text) out of the query itself?

 

[MajP]

as you get the names pass them to the function.

Public Function getSqlString(strQryName As String) As String
getSqlString = CurrentDb.QueryDefs(strQryName).SQL
End Function

 

[vburrows]

Thank you all so very much!!!

I just realized I bid to little to take over a project this size lol