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