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).
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:
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
Query Definition:
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];
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
That code triggers error #3271, "Invalid property value." ... the same error you're seeing.
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;"
... the outcome is still error #3271.
I don't believe there is any way to overcome that Access SQL limitation.
So if the length of your text parameter value is greater than 255 characters, you need a different method.
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
DAO Reference on MSDN — Recordset object
You should explicitly define the query parameters, at least those with LongText data type. Otherwise Access has to guess their data type.
You can do this in the query design editor, click on the "Parameters" button.
Or in SQL view, creating a PARAMETERS clause
PARAMETERS [parLongString] LongText;
UPDATE myTable
SET LongString = [parLongString]
WHERE ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With