1

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?

Diado
  • 2,229
  • 3
  • 18
  • 21
mlomailom
  • 127
  • 1
  • 9
  • 1
    You can't use parameters for table names, period. Needing to use dynamic table names indicates a non-normalized design. The only way to deal with it is through VBA, and I don't recommend integrating VBA in your application if you have 0 experience with it and can't interpret or adjust the code you get in an answer. – Erik A Dec 06 '18 at 08:29
  • Thanks. In that case, I have to use VBA. I'll learn. Could you suggest a VBA code snippet for me? I'll edit the questions accordingly. – mlomailom Dec 06 '18 at 08:35

1 Answers1

2

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 "]]")

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks. Works! I Put the entire thing on to a Form and pimped it up a bit to read input directly from text entry boxes. – mlomailom Dec 06 '18 at 14:19