在SQL Server的WHERE子句中使用长于128个字符的字符串时，MS Access VBA错误3146

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

在Access 2003中，当您构建用于构建记录集SQL字符串时，只要语法正确，就可以了（上限为〜64,000个字符）

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!

如今，如果Len（mycomment）> 128，您会得到一个很好的VBA错误3146-这是一个非常漂亮的通用ODBC错误！

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.

但是一旦您知道了这一点，就可以通过一些错误处理获得更多信息，以返回DBEngine错误，而不仅仅是VBA错误。 您必须通过Google进行“ VBA 3146错误处理”，才能弄清楚这一点，但是，嘿，我们都是天才。

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)

加强错误处理后，您会发现，如果Me.TheCommentBox.Value中包含以下内容，则代码将起作用（它是127个字符）

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)

但是，相同的文本（带有2个额外的“惰性”字词）会爆炸（136个字符）

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.

您的错误处理将为您踢出很多错误。 首先基本上是抱怨您的短语超过128个字符。

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

以-开头的标识符： “敏捷的棕色狐狸跳过了懒狗。 敏捷的棕色狐狸跳过了懒狗。 敏捷的褐狐狸跳过l'- 时间太长。 最多128个字符

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

其余的是Access中有关SQL语句其余部分的TRUNCATION导致的语法错误的错误。

Now what?

怎么办？

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

您确实想要一个具有WHERE子句的记录集，该记录集将新输入的字符串与表中已有的值进行比较。

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

你需要它。 在某些情况下，您不能让用户发布IDENTICAL长字符串-这意味着他们已经复制并粘贴了它们，而不必更新结果（无论如何，这是我的用处）。

What to do?!?

该怎么办？！？

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

好吧，我们可以两步走。 Dosey-do和我们周围。 全方位摆动您的伴侣。

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

在WHERE子句中创建一个记录集，省去长字符串，使其长满。

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

然后将您作为过滤器遗漏的WHERE条件应用于记录集，然后生成您真正想要的记录集。

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.

但这就是生活。 自从A2003以来，使用MS Access便遇到了许多困难。

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

至少通过一点操作，您就可以再次使用曾经工作过的东西。

翻译自: https://www.experts-exchange.com/articles/31265/MS-Access-VBA-error-3146-when-using-a-string-longer-than-128-characters-in-a-WHERE-clause-with-SQL-Server.html