VBA-使用DAO对象的运行时错误3271 [英] VBA - Run Time Error 3271 using DAO object
问题描述
我正在尝试使用DAO.QueryDef
和Microsoft Access中的本地Append
查询更新SQL Server数据库.我正在更新的某些字段包含很长的字符串(从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.
因此,如果您的text参数值的长度大于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
这篇关于VBA-使用DAO对象的运行时错误3271的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!