Table name as a parameter?

  • Thread starter Thread starter Ken Hutson
  • Start date Start date
K

Ken Hutson

Hi Group,

Could someone show me correct syntax for an update query which would ask for
and accept the name of a table as a parameter?

Thanks,
Ken Hutson
San Antonio, Texas
 
Hi Group,

Could someone show me correct syntax for an update query which would ask for
and accept the name of a table as a parameter?
Click to expand...

No, because you cannot use table names as parameters in Access.

What you can do is write VBA code to use InputBox to prompt for a
name, and construct the SQL query from scratch.

The need to do this operation at all suggests that you may be
struggling with a database which stores data in tablenames - do you
have multiple tables of the same structure? If so, revisit your table
design!
 
Ken Hutson said:
Hi Group,

Could someone show me correct syntax for an update query which would
ask for and accept the name of a table as a parameter?

Thanks,
Ken Hutson
San Antonio, Texas
Click to expand...

This is not supported in SQL. The closest you can come is VBA code that
gets the table name, then builds and executes the query. For example:

Dim strTable As String
Dim strSQL As String

strTable = InputBox("Enter table to be updated:")

If Len(strTable) > 0 Then

strSQL = _
"UPDATE [" & strTable & _
"] SET Field1 = Field1 * 2 " & _
"WHERE ID=1"

CurrentDb.Execute strSQL, dbFailOnError

End If
 
John,

Excellent point and something that I had not given much thought to! At the
time this system was deployed, I imagine that disk space was measured in Mb.
As such, I believe the system administrator decided to tailor each table to
the minimum size. Of course, disk space is much less expensive now. He
probably had the idea of preventing some user from screwing up on a wide
basis as well. For example, the effect an erroneous update query would at
least be constrained to one table.

Thanks,
Ken Hutson
San Antonio, Texas
 
Back
Top