Run-Time Error 3161 - recordset field - help

[ferrisbb]

Q: How do you change the default size for a recordset text field? When the recordset is created, the fields are set 50 characters long. I can see the setting in the Locals Window but nothing I do seems to change it. If, for instance, OldValue is greater than 50 characters long, I get a “Run-Time error 3161, The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.” I tried to change Tools, Options, Tables/Queries, Default Field sizes to a larger number but that doesn’t change the property as seen in the locals window. Thanks


Sub UpdateTempRST(txtA, txtFieldName, OldValue, NewValue)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("MyTable&quot
Set rs = db.OpenRecordset("MyTempTable&quot

rs.AddNew
rs!FieldA = txtA
rs!FieldName = txtFieldName

rs!Old = OldValue
rs!New = NewValue
rs.Update
rs.Close
db.Close
End Sub

 

[SonOfEmidec1100]

You have to change the underlying schema of the table. The method will depend on the database used - access, sql server etc.

 

[stoolpigeon]

When I get this error - the data is to large for the field in the database. You don't need to change anything about the recordset, you need to adjust the column size in the db.

 

[jel]

If you open a recordset by opening a table, the field-types and sizes are defined by the table, and you cannot chance them in the recordset: if only because it would be impossible to update the table afterwards.
You might change the table before opening, but that is a far-fetched scenario. You'd better first design your table in a way it can store the data you want to store, then in your app check (and perhaps trim) your values before assigning them to a field.

 

[ferrisbb]

Thanks for the replies. I have tried setting the field size to 255 characters and also to a memo field. Neither of which has any effect on changing the size of the recordset field.

 

[ferrisbb]

I got it. Turns out I am an idiot also.
Thanks for the help.