Update Sqlite table values from VBA created array

Question

I'm trying to UPDATE a table column named "OrigMask" in myfile.sqlite by using an array created in my VBA code. I'm struggling with the syntax. The VBA array is named "NewMask" and has 864 elements as does the table "OrigMask". How do I create the sql statement in VBA and execute. Help is VERY much appreciated !

I'm establishing a statement & connection like so:

Set conn = CreateObject("ADODB.Connection") 

Set rst = CreateObject("ADODB.Recordset")

conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\myfile.sqlite;"

strSQL1 = "UPDATE MyTable SET OrigMask= NewMask;"

rst.Open strSQL1, conn
Set rst = Nothing: Set conn = Nothing

That's not really how SQL works. What exactly are you hoping to achieve with your update? — Tim Williams
– Tim Williams, Commented Nov 23, 2017 at 0:28

Parfait · Accepted Answer · 2017-11-23 01:42:25Z

Several issues here:

You need to iterate through you array and not integrate whole object in SQL as NewMask is unrecognized identifier in database.
An update query is an action query and not a resultset to be retrieved in a recordset.
You need a WHERE clause in UPDATE or all records are update not row by row.

Therefore, consider a For Each loop through a parameterized ADO command object:

strSQL1 = "UPDATE MyTable SET OrigMask = ? WHERE id = ?;"
i = 1

For Each var In NewMask
    Set cmd = CreateObject("ADODB.Command")
    With cmd
       .ActiveConnection = conn
       .CommandText = strSQL1
       .CommandType = adCmdText
       .CommandTimeout = 15
       .Parameters.Append .CreateParameter("maskparam", adVarChar, adParamInput, 255, var)
       .Parameters.Append .CreateParameter("idparam", adInt, adParamInput, , i)
       .Execute
    End With

    i = i + 1
Next var

Collectives™ on Stack Overflow

Update Sqlite table values from VBA created array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related