Error 3271 - Invalid Property Value using DAO.Database with over 255 characters

[easyrider]

Hello all. I have always used Parameterized QueryDef procedures to insert/update tables with a DAO.Database variable. Everything always worked just great.

Until now.

The above error occurs when a text field (memo/notes) contains more than 255 characters - regardless of the LongText type declaration in the table field as well as in the qdef parameter declaration. After some googling I've learned that DAO cannot handle fields that have more than 255 characters and if a "notes" field goes over this limit, it generates the error mentioned in the lead of this post. I've seen some suggestions to use ADO instead and have been comparing & contrasting the differences. Seems like DAO is great for local DB's while ADO is better for client-server applications.

I plan on splitting the DB, in the near future, and putting the back end on the company's SharePoint site then giving users of different departments a front end for any functions that they will need: HR, IT, Management, etc.

Here is a stripped down sample of my existing code:

Private Sub AddUser_BTN_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdef As QueryDef

strSQL = "PARAMETERS " _
& "UserNameVAR TEXT(255), NotesVAR LONGTEXT; " _
& "INSERT INTO User_T " _
& "(UserName, Notes) " _
& "VALUES " _
& "(UserNameVAR, NotesVAR);"

Set db = CurrentDb
Set qdef = db.CreateQueryDef("", strSQL)

qdef.Parameters("UserNameVAR") = Me.UserName
qdef.Parameters("NotesVAR") = Me.Notes

qdef.Execute dbFailOnError
db.Close
Set qdef = Nothing
Set db = Nothing

End Sub


So, my questions are:

1. Should I learn & use ADO instead of DAO?
2. Given that ADO is geared more for client-server applications, would this be relevant in a split DB scenario?
3. I've seen some suggestions to use a procedure w/ DAO.RecordSet instead - particularly with the 'notes" field in question.

What do you guys think?

Thanks,


-Bill

[Gicu]

Hi Bill,
Could you try to save the query as a saved append query without specifying the parameters but simply point to the two form controls? I've been using this method for quite a while with no issues.

Dim qdf as DAO.QueryDef
Dim prm as Parameter

Set qdf=CurrentDb.QueryDefs("qryYourAppendNotes")

For each prm in qdf.parameters
     Prm.Value=Eval(prm.Name)
Next prm

' now run the query
qdf.Execute dbFailOnError

Set qdf=Nothing

Cheers,

[easyrider]

interesting approach, Vlad. Let me work on it tomorrow after the race and I'll report back on my results. Thanks!

[Gicu]

Sure Bill, please note I typed it on my phone and I see some auto-correct errors, I'll try to fix it when I get home to my laptop later today.
Cheers,

[easyrider]

Been playing around with your code Vlad and still getting the 3271 error - but only if I exceed 255 characters in the notes field. On the attached DB, the edit user form has an "Update Notes" button and your code is assigned to it. You'll see that I've placed a couple MsgBox's in the code to indicate what the values are - for T/S purposes.

The notes field has exactly 255 characters. If you open the Edit User form and add 1 more character to it, it throws the error.

CodeTestingDB.zip

[Gicu]

Hi Bill,
Sorry for the delay, I had a look at your sample and you are right, there is a limitation with the DAO parameters that limits them to 255 characters as shown in these links (you've probably already seen them ):
https://stackoverflow.com/questions/...ing-dao-object
https://stackoverflow.com/questions/...-and-ms-access

Anyway, I have incorporated the Eval() function straight into the update query so we can now just call the CurrentDb.Execute method on it. And included few other alternatives just in case.
https://www.devhut.net/ms-access-vba...-query-in-vba/

[easyrider]

Vlad,

Yeah I saw those articles plus some others. Thanks for the Eval function and the alternatives - I need to look into that Eval function some more, especially since I seem to have advanced from "novice" to "advanced beginner." LOL

I tested all four of them and they all work as expected. Do you think I need to be concerned with SQL Injection w/ any of those methods?

[Gicu]

Hi Bill, I am no security expert but I would say you shouldn't worry about SQL injection unless your front-end is exposed to the internet. In a regular network deployment of FE\BE all four methods should be safe.

Cheers,

[easyrider]

I ended up using the 3rd method in Vlad's submission- the DAO.Recordset. For anybody else that stumbles on this thread in the future, the code looks like this and solves the problem that I originally posted:

Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT User_T.* FROM User_T WHERE User_ID = " & Me.User_ID, dbOpenDynaset)
        rs.Edit
        rs("Notes") = Me.Notes
        rs.Update
        rs.Close
   Set rs = Nothing

Thanks, Vlad!

In closing, I'll throw out a little bit of humor that I stumbled on while digging around for an answer - before coming here. It's not exactly relevant to the thread, but funny nonetheless:



link here

[Gicu]