Convertion of sql view of append query to vba points to avoid syntax errors

[sanal]

How to avoid syntax errors in the convertion of sql view of an append query to vba for using in a module and example vba code .

[isladogs]

This is a very broad question so difficult to give a focused answer.
See if my sql to vba converter utility can help with the task
https://www.access-programmers.co.uk...d.php?t=293372

[Peter M]

Agree the question is very broad. But since I do this daily, here is my standard approach. I break it up into chunks and make sure everything is explicitly declared.
(And first write the query using the Wizard, then copy from the SQL view. Depending on the size and complexity, I will use Excel to parse the strings.)
Option Explicit
Dim sqlInsert, sqlSelect, sqlFrom, sqlGroupBy, sqlWhere, sqlHaving, sqlEnd, sqlStr As String

public function sql_Append_CSV(varSourceFile) as string

sqlInsert = sqlInsert & "INSERT INTO " & destTable & " ("
sqlInsert = sqlInsert & " RECORD_TYPE,"
sqlInsert = sqlInsert & " Paid_DT,"
sqlInsert = sqlInsert & " Transaction_NUM,"
etc.
sqlInsert = sqlInsert & ")"

sqlSelect = "Select "
sqlSelect = sqlSelect & " [CSV_LINK].RECORD_TYPE,"
sqlSelect = sqlSelect & " [CSV_LINK].PaiD_DT,"
sqlSelect = sqlSelect & " [CSV_LINK].Transaction_NUM,"
etc.
sqlFrom = " FROM " & varSourceFile & " AS CSV_LINK "

sqlStr = sqlInsert & sqlSelect & sqlFrom ";"
sql_Append_CSV = sqlStr
end function

Watch for SPACES between all your string pieces and NO TRAILING COMMAS (e.g. final select statement).

[ssanfu]

Option Explicit
Dim sqlInsert, sqlSelect, sqlFrom, sqlGroupBy, sqlWhere, sqlHaving, sqlEnd, sqlStr As String

Hmmmmmm...

Something to be aware of:
In VBA, unlike some other programming languages, variables should/must be EXPLICITLY declared.
In the above Dim statement, the variables sqlInsert, sqlSelect, sqlFrom, sqlGroupBy, sqlWhere, sqlHaving and sqlEnd are declared as type Variant......
Only variable sqlStr is declared as type String.

[Micron]

removed duplicate info
ssanfu beat me to it again...

[Peter M]

Hmmmmmm...

Something to be aware of:
In VBA, unlike some other programming languages, variables should/must be EXPLICITLY declared.
In the above Dim statement, the variables sqlInsert, sqlSelect, sqlFrom, sqlGroupBy, sqlWhere, sqlHaving and sqlEnd are declared as type Variant......
Only variable sqlStr is declared as type String.

Ouch! Thank you; this might explain some weird problems I've had where I was sure I had already stated the variable is a string so stop treating it like a number (and vice versa).