VBA ADODB SQL query returns "Automation error" when reading a variable from cell, works well when a value is assigned in VBA code

Question

Debugger returns automation error when running the following :

Private Sub setDB()

Dim SQL As String
Dim Var As String

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.Open "DRIVER={MariaDB ODBC 3.0 Driver}" _
& ";SERVER=" & "localhost" _
& ";DATABASE=" & "pbx" _
& ";USER=" & "root" _
& ";PASSWORD=" & "r00t" _

Var = Worksheets(3).Range("B2").Value  

SQL = "UPDATE ps_product SET ean13='" & Var & "' WHERE id_product=12"
conn.Execute (SQL)

However when I assign a value to Var like this: Var=10, the code runs fine. Am I missing something here? I have searched on the internet for several days and I didn't find anything similar. Can some1 help or maybe send a link to a similar issue, please?

What does `Debug.Print Worksheets(3).Range("B2").Value` return? — BigBen, Oct 26 '20 at 20:58
Curious, in your many days search, did you run into a concept called SQL parameterization? — Parfait, Oct 26 '20 at 21:17
Hi Parfait, no, I am seeing this concept for the first time. So I researched a bit and found some useful information here [link]https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters and in "Using a Connection object" part here: [link]https://learn.microsoft.com/en-us/sql/ado/guide/data/creating-and-executing-a-simple-command?view=sql-server-ver15. Using a connection object or command object seems to behave the same. — Viktor Bendik, Oct 27 '20 at 21:42
Nevermind got it to work after all. Thx for the hint with the parametrization. — Viktor Bendik, Oct 27 '20 at 22:06

score 1 · Answer 1 · edited Oct 27 '20 at 22:23

The query is working when the desired cell is passed as a parameter. (Thx to @Parfait for the hint) Some useful info which helped me figure this out can be found here:

VBA, ADO.Connection and query parameters https://learn.microsoft.com/en-us/sql/ado/guide/data/creating-and-executing-a-simple-command?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/ado/guide/data/passing-parameters-to-a-named-command?view=sql-server-ver15

Working example:

Private Sub setDB()

Dim Cm As New ADODB.Command
Dim Rs As New ADODB.Recordset

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Test = Worksheets(3).Range("B2").Value

CommandText = "UPDATE ps_product SET ean13=? WHERE id_product=12;"

conn.Open "DRIVER={MariaDB ODBC 3.0 Driver}" _
& ";SERVER=" & "localhost" _
& ";DATABASE=" & "pbx" _
& ";USER=" & "root" _
& ";PASSWORD=" & "r00t" _

Cm.CommandText = CommandText
Cm.CommandType = adCmdText
Cm.Name = "Var"
Set Cm.ActiveConnection = conn

conn.Var Test, Rs

End Sub

Parfait · Accepted Answer · 2020-10-28T15:13:02.240

Consider parameterization, the preferred method to bind application layer values to executed SQL queries. ADO supports this approach with Command parameters. This avoids messy concatenation, quote punctation, and escape needs if string value contains special characters like single quotes.

Dim Sql As String, Var As String

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command              ' NEW OBJECT TO INITIALIZE

Set conn = New ADODB.Connection

conn.Open "DRIVER={MariaDB ODBC 3.0 Driver}" _
           & ";SERVER=" & "localhost" _
           & ";DATABASE=" & "pbx" _
           & ";USER=" & "root" _ 
           & ";PASSWORD=" & "r00t" _

' PREPARED STATEMENT WITH PLACEHOLDER (NO QUOTES OR CONCATENATION)
Sql = "UPDATE ps_product SET ean13=? WHERE id_product=12"  

' CONVERT TO NEEDED TYPE 
Var = CStr(Worksheets(3).Range("B2").Value)

Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = conn
    .CommandText = Sql
    .CommandType = adCmdText

    ' BIND PARAMS AND DEFINE TYPE AND LENGTH
    .Parameters.Append .CreateParameter("prm", adVarChar, adParamInput, 255, Var)

    ' EXECUTE ACTION
    .Execute
End cmd

VBA ADODB SQL query returns "Automation error" when reading a variable from cell, works well when a value is assigned in VBA code

2 Answers2