Access client truncating data

Question

Post reply

Access client truncating data

sk_ndiweni

Grasshopper

Points: 21
More actions
December 15, 2008 at 12:21 pm

#197191

I have an access front-end I am running against a SQL- Server 2000 back-end database. The problem I am having is with two fields which I called user_id and Job_user_id which were defined as nvarchar (7). I now have user id's that are of length 8. I thought just going to the table defintions and increasing the length to 10 will take of it but I am still having id's that are 8 characters in length being trancated to 7 in the VBA client causing some of my joins to fail. For instance
Private Sub lstUnassigned_DblClick(Cancel As Integer)
MsgBox Me.cmbJOB_USER_ID
Me.cmbJOB_USER_ID = Me.lstUnassigned
End Sub
The MsgBox shows the JOB_USER_ID trucated to 7 characters instead of 8 that is in the SQL Server database and so the Select statement in the Me.cmbJOB_USER_ID object fails.
I have also checked all my stored procedures and made sure that any user_id references have been increased to 10. I just cannot make the client stop truncating the JOB_USER_ID/user_id fields. Any ideas? Thanks, SKN.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

Jo Pattyn

SSC-Dedicated

Points: 32347

December 16, 2008 at 11:03 am

#913341

Is the table definition in Access updated?

sk_ndiweni Grasshopper Points: 21 More actions · Answer 2

Yes, the tables in access are links to the SQL Server database but they show the correct field sizes (the updated lengths)

sk_ndiweni Grasshopper Points: 21 More actions · Answer 3

I just checked the linked views - they are the ones causing the problem I think, they show Field size of 7 instead of 10. I tried deleting and relinking them thinking they will pick up the new column lengths - they still show a field size of 7 - how do I change that?

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 4

You could recreate the view on the serverbackend and relink the view afterwards in Access.

This should be done whenever underlying datastructure changes, since views in sqlserver keep the definition of their creation until altered.

sk_ndiweni Grasshopper Points: 21 More actions · Answer 5

Yes recreating the views and relinking in Access worked, I stumbled on this same solution the other day. I also had to recreate stored procedures since they were defined with old the field lengths and were also causing data to be truncatied. Thank you all!!!!