Using variables as table names in an append sql statemnt

  • Thread starter Thread starter Rocky
  • Start date Start date
R

Rocky

I am having problems using table name variables (based on a combo box
selection) in a sql statement.

The code below shows the code that works without the variable and then
the code that doesn't work.

Private Sub Audit_Click()

'Start Error Enabler
On Error GoTo PROC_ERR
'End Error Enabler

'Start Code
Dim dbsMyDB As DAO.Database
Dim TblUsed As String
Dim strVar As String

TblUsed = Forms![Reader_Audit_Report]![FileName]

DoCmd.SetWarnings False
DoCmd.OpenQuery "Reader_Audit_Delete", acViewNormal, acEdit

'The code below works fine, but needs to be replaced by a
variable based
'on the value of combo box on the form - see code below
' "SELECT Reader_DistrList_Audit_Temp.* " & _
' "FROM Reader_DistrList_Audit_Temp;"

'This code uses the variable, but returns a compile error
(Expected: end of statement)
strVar = "INSERT INTO Reader_DistrList_Audit " & _
"SELECT ["& TblUsed&"] .*" & _
FROM ["&TblUsed&"] ;"

DoCmd.RunSQL strVar
DoCmd.Close acQuery, "Reader_Audit_Delete"
DoCmd.OpenReport "Audit_Comparison", acViewPreview, "", "",
acNormal

'End Code

'Start Error Exit
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description, vbCritical, Me.Name & ".Audit_Click"
Resume PROC_EXIT
'Error Procedure End

End Sub

I would appreciate if someone could assist
 
Rocky said:
I am having problems using table name variables (based on a combo box
selection) in a sql statement.

The code below shows the code that works without the variable and then
the code that doesn't work.

'This code uses the variable, but returns a compile error
(Expected: end of statement)
strVar = "INSERT INTO Reader_DistrList_Audit " & _
"SELECT ["& TblUsed&"] .*" & _
FROM ["&TblUsed&"] ;"
Click to expand...

For one thing, you have a single table in the FROM clause so there is
absolutely no need to qualify the wildcard in the SELECT clause. There's
only one place the fields can come from.
Your problem is you are missing a quote before the word FROM.

Adding some whitespace to make it more readable, I would to this:

strVar = "INSERT INTO Reader_DistrList_Audit " & _
"SELECT * FROM [" & TblUsed & "] ;"
debug.print strvar
 
Back
Top