MS Access VBA error 3146 when using a string longer than 128 characters in a WHERE clause with SQL Server

*Sigh*

Someday there will be progress.

Not today apparently.

In Access 2003, when you went to build a SQL string for building a recordset, as long as it was syntactically correct, you were OK (the upper limit was ~64,000 characters)

Dim db As Database
Dim rs As Recordset
Dim SQL As String
Dim mycomment As String
mycomment = Me.TheCommentBox.Value
SQL = "Select tblSomeTable.* from tblSomeTable WHERE tblSomeTable.Comment = " & mycomment    
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
'-do some nice stuff with your recordset

These days, if Len(mycomment) > 128, you'll get a nice VBA error 3146 -- which is a beautifully generic ODBC error!

Just look at the help for that lovely error! Doesn't that tell you exactly what went bang?  No!

But once you know that, you can get some more info with some error handling to return the DBEngine errors and not just the VBA ones.  You'd have to Google up 'VBA 3146 error handling' to figure that out, but hey, we're all geniuses.

Dim db As Database
Dim rs As Recordset
Dim SQL As String
Dim mycomment As String
On Error GoTo MyErr

mycomment = Me.TheCommentBox.Value
SQL = "Select tblSomeTable.* from tblSomeTable WHERE tblSomeTable.Comment = " & mycomment    
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)

'-do some nice stuff with your recordset

Exit_myErr:
    
Exit Sub

MyErr:    
Dim myerror As Error    
For Each myerror In DBEngine.Errors
   With myerror            
        If .Number <> 3146 Then                
            MsgBox .Description            
    End If        
 End With    
Next   
 Resume Exit_MyErr

Once you have your beefed-up error handling in place you'll discover that if you have the following in Me.TheCommentBox.Value, your code will work (it's 127 characters)

The quick brown fox jumps over the lazy dog.  The quick brown fox jumped over the dog.  The quick brown fox jumps over the dog.

However, the same text (with the 2 extra "lazy" words) will go bang (136 characters)

The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.

Your error handling will kick you a bunch of errors.  The first is basically complaining that your phrase is over 128 characters.

The identifier starting with -- 'The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the l'-- is too long.  The maximum is 128 characters

The rest are errors from Access about bad syntax resulting from the TRUNCATION of the rest of your SQL statement.

Now what?

You really want a recordset with a WHERE clause that compares a newly entered string to values already in the table.

You need it.  You  can't have the users posting IDENTICAL long strings in some cases -- it means they've copied and pasted without necessarily updating the result (that's my use for this at any rate).

What to do?!?

Well, we can two step. Dosey-do and around we go. Swing your partner round-and-round.

Create the recordset leaving out the long string in the WHERE clause that makes it go bang.

Then apply that WHERE condition you left out as a filter to the recordset and generate the recordset you REALLY want afterward.

Dim db As Database
Dim rs As Recordset
dim rs1 as recordset
Dim SQL As String
Dim mycomment As String
On Error GoTo MyErr

mycomment = Me.TheCommentBox.Value
SQL = "Select tblSomeTable.* from tblSomeTable;"      
Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
rs1.Filter = "tblSomeTable.Comment = " & mycomment"
Set rs = rs1.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'-do some nice stuff with your recordset you really wanted in the first place.

Exit_myErr:
    
Exit Sub

MyErr:    
Dim myerror As Error    
For Each myerror In DBEngine.Errors
  With myerror            
        If .Number <> 3146 Then                
            MsgBox .Description            
    End If        
End With    
Next   
Resume Exit_MyErr

Now, as noted, this is painful.

But such is life.  With MS Access, much has been downhill and backward since A2003.

At least with a little manipulation you can get what used to work to work once more.