Using TABLE name as textbox value

[vadharah]

Using textbox value as TABLE name

Hi guys,

im trying to specify the table name in my query through a textbox i.e. the name of table to query from will be specified in the text field.

so far i have this:


SELECT *
FROM forms!search!table_name.value;

im getting an error saying 'Microsoft Jet database engine cannot find the input table or query forms!search!table_name.value'

what am i doing wrong?

 

[vadharah]

in VBA or Access design view for a SELECT QUERY is it posiible to specify the TABLE NAME as a variable????

 

[Rabbie]

I don't think it will work in design view but in VBA you should be able to do something like

strSQL = "SELECT * FROM " & forms!search!table_name.value

docmd.runSQL strSql

 

[vadharah]

I don't think it will work in design view but in VBA you should be able to do something like

strSQL = "SELECT * FROM " & forms!search!table_name.value

docmd.runSQL strSql

Thank you for the reply Rabbie.

i have tried this

Dim strSQL As String

strSQL = "SELECT * FROM " & forms!search!table_name.value
docmd.runSQL strSql

and im getting an error saying " A RunSQL action requires an argument consisting of an SQL statement"

 

[Rabbie]

Sorry I missed something. You cant run a SELECT query like this. Docmd.RunSQL can only run Action Queries.

If you want to change/update the SQL in a stored query then see this code

Function AmendQueryDef(strQuery As String, strSQL As String) As Boolean
' This function is used to amend an existing query to change its SQL
' There are 2 parameters
' strQuery - This is the query to be changed
' strSQL   - This is the new SQL for the query
Dim qdf As QueryDef
' Exit if no SQL has been set.
If strSQL = "" Then Exit Function
' Change the query
Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow
AmendQueryDef = True
End Function

 

[Atomic Shrimp]

Maybe it's none of my business (feel free to say so)... Could I ask why you need to be able to select a table name like that? Reason I ask is that it sounds like maybe you have a bunch of similar tables, all with the same structure - which, if it is the case, is perhaps not the best way to organise your data.

 

[vadharah]

Thanx for the reply Mike,

The structures are different my idea was initially to create a form where a user specifies to search data from then i use the variable in my select Query. But then again im not sure how i will sort the fields' issue. Im still learn and guess the best way to do so is through blunders and advice