VBA - Run Time Error 3271 using DAO object

Question

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];

Mildew

Assuming qryUpdateFile is a local Access object, does a plain vanilla INSERT statement with long texts work, typed into the SQL view of a new query? (Without using VBA objects) – Macropterous

@Macropterous Yes this had worked before. I did have to escape certain characters such as ' and " which was a pain. It's also open to SQL injection which is why I have chosen to revise my old code and use parameterized queries. qryUpdateFile is a local access object, I am not calling any SQL stored procs or anything for now. – Mildew

Can you add the full SQL of qryUpdateFile (including the PARAMETERS section) to your question? – Lilylivered

Well, this might be DAO not correctly interpreting text fields with more than 255 characters length. What does qdf.Fields("ProblematicField").Size return? And as what type does the querydef interpret this field? (qdf.Fields("ProblematicField").Type) – Macropterous

@Lilylivered I have updated my OP to include the full SQL statement – Mildew

@Macropterous My usage is probably wrong because I'm getting a Item not found in this collection. I'm just declaring an integer and assigning it like so: fieldSize = qdf.Fields("SQLStatement").Size. I have a breakpoint set which breaks with the exception. – Mildew

dbs.QueryDefs("qryUpdateFile") returns a querydef object based on the Acces object "qryUpdateFile". dbs.QueryDefs("qryUpdateFile").Fields("fldFileNameLogic") returns a field object based on the field "fldFileNameLogic" in "qryUpdateFile". You have to specify the field name, not an SQL statement. – Macropterous

There is no PARAMETERS statement in the update query. How are you defining them? Please also break lines in query to avoid horizontal scrolling. Also what line does debugger highlight? – Hallow

@Hallow I have fixed the horizontal scrolling issue. I have not defined any PARAMETERS within the query itself. This was my first time trying to figure out this concept in MS Access and I think I misunderstood how they should be set up. The debugger in VBA will highlight any line in which a parameter length is over 255 characters. For example, if I am updating the field Note and the textbox containing the text to update is 1000 characters long, the exception will be thrown there. – Mildew

@Macropterous My parameter, SQLStatement, is not an actual SQL statement that is used for anything. It is simply just a text field that can be very large. It should have been fldSqlStatement according to your response. fldFileNameLogic is a column inside my database and FileNameLogic is the value I am passing into the query. – Mildew

So now, what do qdf.Fields("fldFileNameLogic ").Size and qdf.Fields("fldFileNameLogic ").Type return? – Macropterous

@Macropterous I'm receiving the same Item not found in this collection error. i.imgur.com/zHHXFVY.jpg – Mildew

But there is a field named fldFileNameLogic in qryUpdateFile, right? Btw., test1 and test2 cannot be objects in the code from the screenshot. Long should be ok, Variant will definetely work. – Macropterous

@Macropterous That is correct. I have a table tblFile with a column fldFileNameLogic - this contains the name of a file. This field should be the first field in my query qryUpdateFile. – Mildew

Strange... however, this probably isn't even important, seeing HansUp's answer. If you think otherwise, we can still follow up on this. – Macropterous

Answer 1

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

Extravagancy

I will most likely have to take this route. Thank you for the detailed explanation. – Mildew

Wow, that's interesting. Makes you wonder why LongText is a valid parameter data type at all. – Lilylivered

I wondered about that, too, @Andre. But I still don't see where LongText is useful. I think it would be great if someone can tell us. :-) In the meantime, at least DAO.Recordset.Edit works. ;-) – Extravagancy

@Lilylivered It seems that ADO doesn't have this issue. – Aldridge

Answer 2

In addition to HansUp's answer, it seems that ADO doesn't have this issue.

Add a reference (Tools -> References...) to the Microsoft ActiveX Data Objects library (choose the highest version; on my machine it's 6.1).

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.AccessConnection
cmd.CommandText = "qryUpdateFile"

'the rest of the parameter values need to be included in the array
'omitted for brevity
cmd.Execute , Array(txtboxUpdateConversionName.Value, txtboxZipFileNameLogic.Value)

ADO Reference on MSDN

• Command object
• Command.Execute method

Aldridge

Answer 3

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

Lilylivered