SQL Query in Access to prompt with Message Box asking to change table name

Question

Is there a way to be prompted before you a run an SQL query in Access, to enter in the table name that you wish to query? For example, lets say the columns will always stay constant. The columns could be called "Fruit" and "Date." But, the table name could change depending on the batch number. Ie. table name could be "BatchNO_1" or "BatchNO_2" or "BatchNO_3" etc. So Lets say i have an SQL like:

select Fruit, Date from BatchNO_1 where Fruit = "Apples"  

Is there a way that I can be prompted to enter in the table name and have the SQL use the table name i enter to perform the query?

Answer 1

No. The table name cannot be passed as parameter to a query. You will have to construct the query yourself.

Dim tableName as String, sql As String

tableName = InputBox("Please enter the table name")
If tableName <> "" Then
    sql = "SELECT Fruit, Date FROM [" & tableName & "] WHERE Fruit = 'Apples'"
    'TODO: execute the query here
End If

For instance, you could change the query text of an existing query like this:

CurrentDb.QueryDefs("myQuery").SQL = sql

Or you could execute the query like this

Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
    Debug.Print rs!Fruit & " " & rs!Date
    rs.MoveNext
Loop
rs.Close: Set rs = Nothing
db.Close: set db = Nothing