ADO Execute SQL string length

[mikeyanagita]

ADO Execute SQL string length (SOLVED)

I'm trying to send values from an excel workbook to an Access database using VBA. Problem is that sometimes strLongString gets really long, and apparently the Execute command can't use any SQL string longer than ~306 characters, so the SQL statement gets truncated and doesn't make any sense.

NB that field1 is a memo field, so that shouldn't be the problem. The problem is the SQL statement getting cut short.

Any workaround? Can I make Execute method accept longer strings? Or any way to send these values to Access without using an SQL string?

Thanks in advance.

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & strPathToMDBFile)
conn.Open

'...
'...
'...

'now the SQL statement where I send data to the db
conn.Execute "UPDATE [tbl_EIQ_Point_Information] " & _
             "SET [field1] = '" & strLongString & "' " & _
             "WHERE [field2] = '" & strField2Value & "' AND [field3] = '" & strField3Value & "'"

 

[boblarson]

Use this:

Dim strSQL As String
 
strSQL = "UPDATE [tbl_EIQ_Point_Information] " & _
             "SET [field1] = '" & strLongString & "' " & _
             "WHERE [field2] = '" & strField2Value & "' AND [field3] = '" & strField3Value & "'"
 
conn.Execute strSQL

You have more available to you that way.

 

[mikeyanagita]

thanks. That's probably a good practice.

But after some investigating, it turns out that length isn't the issue. It's special characters in my string like * and '. Parameters should fix it (found in link below).

http://www.vbforums.com/showthread.php?t=552665

 

[boblarson]

You can deal with the ' in your concatenation by using Chr(34) instead, so if you use something like

"WHERE [field2] = '" & strField2Value & "' AND

you can use

"WHERE [field2] = " & Chr(34) & strField2Value & Chr(34) & " AND

And it will work better for you. In fact, I've gone pretty much to doing that all of the time in new code so I don't have to worry about the single quotes problem. Don't know about the asterisks.