Robert Mazurowski Robert Mazurowski - 22 days ago 7
SQL Question

MS Access SQL How to put a table name as Variable (how to run one query for multiple tables)

Lets say I have a query like this:

Parameters Table_Name string, Field_Name string;

Update Table_Name Set Table_Name.[field1] = "new value", Table_Name.[field2] = "new Value" Where Table_Name.[Field_Name] = "Some value"


Basically I have the same query which I need to run against different tables which share some fields together. I want to be able to type the table name when I run the query.

I know this can be achieved with VBA, but this way would be a lot easier than VBA. Although VBA is also welcome but I would like to be able to do this in pure SQL.

How to achieve the above logic for table names AND field names?

Answer

Is this being done in MSAccess or in SQL Server? If you've got a SQL Server behind things, then you'll be looking to do some sp_executesql calls to get the job done. If you're doing this in MSAccess, you can accomplish the same by building dynamic SQL statements in VBA - either way would work if you've got Access pointing to SQL, though.