Search code examples
sqlms-accessjet-sql

Run one MS Access SQL script on a particular Table chosen by user

I have a MS Access 2016 database (*.accdb) with 20+ Tables. Fields in each of them vary slightly from Table to Table. I've no VBA experience, so I'm sticking only to the SQL query below (redacted).

SQL script

myvar below is the parameter I'd like to be prompted when the script is run so that I enter the Table I want the changes applied to.

PARAMETERS 
[myvar] TableID;

UPDATE 
[myvar]

INNER JOIN  
Excel_Data ON [myvar].[Part Number] = Excel_Data.[Part Number] 

SET 
[myvar].[Value] = '?', 
[myvar].Description = Excel_Data.Description, 
[myvar].[Ref] = '?'
.
.
.

WHERE 
[myvar].Description Is Null;

Output

Error message:

Too few parameters. Expected 0.

What I need

I prefer a solution for above in a SQL script form as above, not involving VBA, preferably. I'd like to enter the Table name when prompted so the script knows which table to UPDATE. FYI: The PARAMETERS work when it is not a Table as I've shown in my script above.

Help/advise is highly appreciated.

EDIT 1

Since it seems not possible to use parameters as Table names, could you suggest a VBA solution? A sample code, perhaps?

Solution

  • As said in the comments, you can't really solve this without VBA.

    You can store your SQL query in a string, and use a placeholder to indicate the tablename. Then get the tablename using an inputbox and replace the placeholder with the tablename.

    Dim sqlString As String
sqlString = "UPDATE [%Placeholder%] "  & vbCrLf & _
"INNER JOIN Excel_Data ON [%Placeholder%].[Part Number] = Excel_Data.[Part Number]  "  & vbCrLf & _
"SET [%Placeholder%].[Value] = '?', " & vbCrLf & _
...
"WHERE [%Placeholder%].Description Is Null;"
sqlString = Replace(sqlString, "%PlaceHolder%", InputBox("Enter a tablename"))
CurrentDb.Execute sqlString

    In a more mature solution, I'd create a form with a combobox containing all available table names, and add a function to sanitize tablenames (replace "]" with "]]")