VBA - 使用 DAO 对象时出现运行时错误 3271 [英] VBA - Run Time Error 3271 using DAO object

问题描述

我正在尝试使用 DAO.QueryDef 和 Microsoft Access 中的本地 Append 查询来更新 SQL 服务器数据库.我的一些正在更新的字段包含非常长的字符串(从 0 到 700 个字符以上的任意位置).

I'm trying to update a SQL server database using DAO.QueryDef and a local Append query in Microsoft Access. Some of my fields that are being updated contain very long strings (anywhere from 0 to upwards of 700 characters).

当字符串长度在 0 到 255 个字符的范围内时，我可以将它传递到我的查询中并更新相应的表.但是，当它们超过 255 个字符时，我收到以下运行时错误:

When the string length is in the range from 0 to 255 characters, I have no problem passing it into my query and updating the respective tables. However when they exceed 255 characters, I receive the following run-time error:

我一直在使用一个随机字符串生成器网站 创建和测试不同长度的字符串.我还检查了我的数据库中的列数据类型，它们都是 NVARCHAR(MAX) 需要的地方.Microsoft Access 显示数据类型为 Long text 的相同列.

I have been using a random string generator website to create and test strings with varying lengths. I have also checked my database for the column data types and they are all NVARCHAR(MAX) where they need to be. Microsoft Access is showing the same respective columns with the data type Long text.

请参阅下面的代码片段:

See below for my code snippet:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

If Not IsNull(cmbboxFileNameLogic) Then
    Set qdf = dbs.QueryDefs("qryUpdateFile")

    qdf.Parameters("FileName").Value = txtboxUpdateConversionName.Value
    qdf.Parameters("ZipFileName").Value = txtboxZipFileNameLogic.Value
    qdf.Parameters("OutputFormat").Value = txtboxOutputFormat.Value
    qdf.Parameters("Delimeter").Value = txtboxDelimeter.Value
    qdf.Parameters("DestinationLocation").Value = txtboxDestinationLocation.Value
    qdf.Parameters("DeliveryMechinism").Value = txtboxDeliveryMechinism.Value
    qdf.Parameters("Note").Value = txtboxOutputFileInfoNotes.Value
    qdf.Parameters("Criteria").Value = txtboxOutputFileInfoCriteria.Value
    qdf.Parameters("CustomListKey").Value = txtboxCustomListKey.Value
    qdf.Parameters("ExcludeCustomListKey").Value = txtboxExcludeCustomListKey.Value
    qdf.Parameters("NewspaperFlag").Value = chkNewsPaperFlag.Value
    qdf.Parameters("WebsiteFlag").Value = chkWebsiteFlag.Value
    qdf.Parameters("MarketingFlag").Value = chkProfessionalMarketingFlag.Value
    qdf.Parameters("PrintFlag").Value = chkProfessionalPrintFlag.Value
    qdf.Parameters("WebsiteFlag").Value = chkWebsiteFlag.Value
    qdf.Parameters("BrokerDealerFlag").Value = chkBrokerDealerFlag.Value
    qdf.Parameters("ActiveOnly").Value = chkActiveOnly.Value
    qdf.Parameters("OutputFormatting").Value = txtboxFileFormatting.Value
    qdf.Parameters("Header").Value = txtboxHeader.Value
    qdf.Parameters("Footer").Value = txtboxFooter.Value
    qdf.Parameters("SQLStatement").Value = txtboxSQLStatement.Value
    qdf.Parameters("OrderBy").Value = txtboxOrderBy.Value
    qdf.Parameters("FileID").Value = cmbboxFileNameLogic.Value

    qdf.Execute dbSeeChanges
    qdf.Close

    lblOutputFileInfoAction.Caption = "File successfully updated"
    lblOutputFileInfoAction.Visible = True

Else
    -- Insert new values
End If

查询定义:

UPDATE myTableNameGoesHere SET fldFileNameLogic = [FileName], 
fldZipFileNameLogic = [ZipFileName],fldOutputFormat = [OutputFormat],
fldDelimeter = [Delimeter], 
fldDestinationLocation = [DestinationLocation], fldDeliveryMechinism = [DeliveryMechinism], 
fldNote = [Note], fldCriteria = [Criteria], fldCustomListKey = [CustomListKey],
fldExcludeCustomListKey = [ExcludeCustomListKey], fldNewspaperFlag = [NewspaperFlag], 
fldProfessionalWebsiteFlag = [WebsiteFlag], fldProfessionalMarketingFlag = [MarketingFlag], 
fldProfessionalPrintFlag = [PrintFlag], fldWebsiteFlag = [WebsiteFlag], 
fldBrokerDealerFlag = [BrokerDealerFlag], fldActiveOnly = [ActiveOnly], 
fldFileOutputFormatting = [OutputFormatting], fldHeader = [Header], 
fldFooter = [Footer], fldSQLStatement = [SQLStatement], fldOrderBy = [OrderBy]
WHERE [fldFileID] = [FileID];

推荐答案

您正面临 Access SQL 文本参数的限制.它们不能容纳超过 255 个字符的字符串值.

You're facing a limitation of Access SQL text parameters. They can not accommodate string values longer than 255 characters.

这是一个演示问题的简单示例.

Here is a simple example which demonstrates the problem.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strUpdate As String
Dim strLongString As String
strLongString = String(300, "x")
strUpdate = "UPDATE tblFoo SET memo_field = [pLongString] WHERE id=2;"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strUpdate)
qdf.Parameters("pLongString").Value = strLongString
qdf.Execute dbFailOnError

该代码触发错误 #3271，无效的属性值." ...与您看到的错误相同.

That code triggers error #3271, "Invalid property value." ... the same error you're seeing.

如果我将 UPDATE 语句更改为包含这样的 PARAMETERS 子句...

If I change the UPDATE statement to include a PARAMETERS clause like this ...

strUpdate = "PARAMETERS [pLongString] LongText;" & vbCrLf & _
    "UPDATE tblFoo SET memo_field = [pLongString] WHERE id=2;"

...结果还是错误#3271.

... the outcome is still error #3271.

我认为没有任何方法可以克服 Access SQL 限制.

I don't believe there is any way to overcome that Access SQL limitation.

因此，如果您的文本参数值的长度大于 255 个字符，则需要不同的方法.

So if the length of your text parameter value is greater than 255 characters, you need a different method.

DAO.Recordset 方法是在字段中存储长文本字符串的简单替代方法.

A DAO.Recordset approach is a simple alternative to store long text strings in a field.

Dim rs As DAO.Recordset
Dim strSelect
strSelect = "SELECT id, memo_field FROM tblFoo WHERE id=2;"
Set rs = db.OpenRecordset(strSelect)
With rs
    If Not (.BOF And .EOF) Then
        .Edit
        !memo_field.Value = strLongString
        .Update
    End If
    .Close
End With

MSDN 上的 DAO 参考 —记录集对象

这篇关于VBA - 使用 DAO 对象时出现运行时错误 3271的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！