VBA – Run Time Error 3271 using DAO object

by

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

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  8. How to diagnose MS access crashes
  9. Equivalent cURL in VBA?
  10. How do I access the selected rows in Access?
  11. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  12. Prevent enabling of shift bypass – Access 2010
  13. VBA procedure to import csv file into access
  14. Access – compare two tables and update or insert data in first table
  15. Transform multiple rows into columns with unique key
  16. how to show query results in a datasheet in the same form?
  17. Exporting MS Access Forms and Class / Modules Recursively to text files?
  18. Access VBA: Find max number in column and add 1
  19. MS Access prepared statements
  20. Create sequential ID value based on the year that a record is added
  21. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  22. Null values for variables in VBA
  23. Using a centralized VBA module in multiple Access databases
  24. How to execute a query in ms-access in VBA code?
  25. How to add a menu item to the default right click context menu
  26. Trouble with InputBoxes
  27. Get Name of Current VBA Function
  28. How to write Case Sensitive Query for MS Access?
  29. How to copy to clipboard using Access/VBA?
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment