VBA - Run Time Error 3271 using DAO object
<p>I'm trying to update a SQL server database using <code>DAO.QueryDef</code> and a local <code>Append</code> query in Microsoft Access. Some of my fields that are being updated contain very long strings (anywhere from <code>0</code> to upwards of <code>700</code> characters).</p>

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

<p><a href="https://i.stack.imgur.com/zGzgW.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zGzgW.jpg" alt="VBA Run-time error 3271"></a></p>

<p>I have been using a <a href="http://textmechanic.com/text-tools/randomization-tools/random-string-generator/" rel="nofollow noreferrer">random string generator website</a> to create and test strings with varying lengths. I have also checked my database for the column data types and they are all <code>NVARCHAR(MAX)</code> where they need to be. Microsoft Access is showing the same respective columns with the data type <code>Long text</code>.</p>

<p>See below for my code snippet:</p>

<pre><code>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
</code></pre>

<p>Query Definition:</p>

<pre><code>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];
</code></pre>