How to increase performance for bulk INSERTs to OD

This question is based on on this one: How to increase performance for bulk INSERTs to ODBC linked tables in Access?

I have further queston for the linked topic above, since I do not have enough score to comment on the topic, so I created my question here.

With the excellent answer from Gord Thompson, who provided the following codes in the link above.

Sub PtqTest()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim t0 As Single, i As Long, valueList As String, separator As String

t0 = Timer
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP", dbOpenSnapshot)
i = 0
valueList = ""
separator = ""
Do Until rst.EOF
    i = i + 1
    valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")"
    If i = 1 Then
        separator = ","
    End If
    If i = 1000 Then
        SendInsert valueList
        i = 0
        valueList = ""
        separator = ""
    End If
    rst.MoveNext
Loop
If i > 0 Then
    SendInsert valueList
End If
rst.Close
Set rst = Nothing
Set cdb = Nothing
Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

Sub SendInsert(valueList As String)
Dim cdb As DAO.Database, qdf As DAO.QueryDef

Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("METER_DATA").Connect
qdf.ReturnsRecords = False
qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing
End Sub

My question is what if I want to include multiple columns during the insert? For example, apart from MPO_REFERENCE, I have other column named NEW_COLUMN wants to be inserted to METER_DATA (odbc linked table from sql server), and I tried following modifications on some of the lines in above codes but failed. Sorry I am new to SQL and VB, much appreciate if someone can help. Thanks.

    ...
    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE,NEW_COLUMN FROM tblTempSmartSSP", dbOpenSnapshot)
    ...
    ...
        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & "," & rst!NEW_COLUMN & ")"

    ...
    ...
    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE,NEW_COLUMN) VALUES " & valueList

    ...

Basically, you got the idea and the code is correct.

But there are some other things you must consider:

If rst!NEW_COLUMN is empty, you pass an empty string to the SQL command and it will cause an error.

e.g. error causing SQL-string:

INSERT INTO METER_DATA (MPO_REFERENCE,NEW_COLUMN) VALUES (3, )

Check if NEW_COLUMN is empty before building the SQL string
If NEW_COLUMN is a string value, it must be set between single quotation marks. This would look like that:

valueList = valueList & separator & "(" & rst!MPO_REFERENCE _ & ",'" & rst!NEW_COLUMN & "')"

The second consideration will probably also solves the first consideration, because it changes ...VALUES (3, )... to ...VALUES (3, '')... which provides an empty string insert.

I assume, MPO_REFERENCE works without surrounded single quotation marks because it is a number.