Parameter Query question 2

[teach314]

hello
In a saved query, is there any way to use a parameter for a table's name in the FROM statement.
I get an error message (Parameter 'Enter_Table_Name' specified where a table name is required) when I try this...

PARAMETERS [Enter_Table_Name] Text(255);
SELECT t.X, t.Y, COUNT(*) AS Z
FROM  [Enter_Table_Name] t
GROUP BY t.X, t.Y;

I use this as a quick but frequent diagnostic test, so I don't really want to build the query in VBA.
Can it be done in a saved query?

thanks in advance
Teach

 

[dhookom]

I doubt it can be done as hoped.

Consider a simple generic function that you can reuse again and again.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

No it cannot be done nor can you do it with field names.

 

[MajP]

Here is code to do this. I would make a form with a button on it. Have a subform with a datasheet. Then you can change it on the fly

Public Sub ChangeTableQuery()
  Const queryName = "myQuery"
  Dim tableName As String
  Dim tdf As TableDef
  Dim qdf As QueryDef
  Dim validTable As Boolean
  Dim existingQuery As Boolean
  Dim continueCancel As Long
  Dim strSql As String
  Dim CDB As DAO.Database

  'Get and Verify Table
  Do
     tableName = InputBox("Enter table Name", "Enter Table Name")
     For Each tdf In CurrentDb.TableDefs
       If tableName = tdf.Name Then
         validTable = True
        Exit For
      End If
     Next tdf
     If Not validTable Then
       continueCancel = MsgBox("Table " & tableName & " is not valid. Enter new table name or cancel to exit.", vbOKCancel, "Invalid Name")
       If continueCancel = vbCancel Then Exit Sub
     End If
  Loop Until validTable = True
  'See if the existing qdf exists
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = queryName Then
      existingQuery = True
      Exit For
    End If
  Next qdf
  'some sql string
  strSql = "Select * from " & tableName & " order by 2"
  Set CDB = CurrentDb
  If Not existingQuery Then
    Set qdf = CDB.CreateQueryDef(queryName, strSql)
  Else
    Set qdf = CDB.QueryDefs(queryName)
    qdf.SQL = strSql
    CurrentDb.QueryDefs.Refresh
  End If
  DoCmd.Close acQuery, queryName
  DoCmd.OpenQuery qdf.Name
End Sub

 

[teach314]

Nicely done, MajP! Very instructive.