Access 2016: Syntax error on passing variant [table name] to SQL string

Question

Researched both text books and Google to find Access SQL rules of syntax on passing a [table name] variant to a SQL statement inside FOR LOOP.

Sub MergeAllAccounts()

Dim vnt As Variant

Dim vnt2 As Variant

Dim MySQL_Append

Dim test As String

With CurrentData

        For Each vnt In .AllTables

         If Left(vnt.Name, 4) = "Acct" Then

         Debug.Print vnt.Name

        'MySQL_Append = "INSERT INTO A1_MainData SELECT * FROM Acct_7840_12_12_2016" '<<<<Static line of code works.

         MySQL_Append = "INSERT INTO A1_MainData SELECT * FROM '" & [vnt2] & "';" '<<< {3450 Error, incomplete query clause.

         DoCmd.SetWarnings False

         DoCmd.RunSQL MySQL_Append

         DoCmd.SetWarnings True

        End If

        Next

    End With

End Sub

Answer 1

Daniel beat me to it. since your table names have spaces you need to concatenate the brackets around it. As to the semi colon on the end of the statement, Access uses that to denote the end of the statement. Its not always necessary, but I always include it for consistency.

Answer 2

Sorry, just spotted the problem.

It should be

vnt

not

vnt2

There was a typo in your original code.

So try

MySQL_Append = "INSERT INTO A1_MainData SELECT * FROM [" & vnt.Name & "];"

Answer 3

Error fails when the DoCmd.Run  executes. I'm having difficult understanding the SQL syntax with variables in Access's VBA.

Answer 4

I believe it should be

MySQL_Append = "INSERT INTO A1_MainData SELECT * FROM [" & vnt2.Name & "];" '<<< {3450 Error, incomplete query clause.

Answer 5

Do you get the error on the line shown or on the line "DoCmd.RunSQL MySQL_Append"? I'm assuming the latter.

You don't need the single quotes in the second line. Looking at the static line, there are no quotes inside the string. In the second there are. Just look at the value of MySQL_Append after the line has been executed and you'll see it's not the same.