<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

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

Published on
3,142 Points
142 Views
Last Modified:
Sometimes MS breaks things just for fun...
In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset.
Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters.
So, this article addresses the 'improvement'...

*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.

0
Comment
Author:Nick67
0 Comments

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month