none
file path getting cutoff RRS feed

  • Question

  • Greetings.  I have field on a form that has the full path to a file using the open dialog method.  This works fine.  The text box shows the full path to the file.  I update the table and the full path is in the table field.  When I bring the record back out to the form the file path is now cut off at 50 characters.  A check and the table still has the full path.  The table is sql server and a varchar(250) for datatype.  I am lost as to why the path is stopping at 50. 

    Anyone have any thoughts on why this is occurring as I sure don't?

    Thanks....John

    Tuesday, September 15, 2015 5:02 AM

Answers

  • Thanks all for responding.  I traced my way thru from updating the field in the table back to the field on the form.  I updated the field with the full path (way more than 50).  Went to the table and full path was there.  Went to the view in sql server that fills the listbox on the form.  The full path was there.  Looked at the linked view in Access.  The full path stopped at 50.  Used the linked table manager to update, still stopping at 50.  Deleted the view, relinked, reupdated with link manager, still stopping at 50.  Went into the design of the linked view in Access and it shows the field as Short Text with a length of 50.  Can't change the design in Access, it has to be in sql server.  The view is showing full path.  There is another field that is also a varchar(250) and that is not being stopped at 50.  Looked at it and it is Short Text with a field size of 250. So, I went back to the view in sql server and ran it and the full path was there.  Hovered over the field in design and it showed varchar(250).  I then unclicked the field and rechecked it to show in the view.  Put it back in order.  Deleted the link, then relinked the view and ran the link manager.  Opened up the view in Access and the full path was there.  Looked at the design and it was Short Text with field size of 250.  Problem solved.  But not sure why the view would not update thru the link manager?  Any way, problem solved so time to move on.

    Again, thank you all for the reply.

    John

    Tuesday, September 15, 2015 2:11 PM
  • Views in SQL Server tend to not see table design changes until refreshed. Therefore both SQL Server and Access will see the view with the old column size until both the view is refreshed AND Access relinks to the view.

    Access Will also fail to see changes made to linked tables until the link is refreshed. One of the routines I keep handy is a SQL Server link refresher. Anytime I change a linked object in SQL Server I run the routine to relink everything in the Access side. Usually this is only done early on in the application design stage.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 15, 2015 5:39 PM

All replies

  • When I bring the record back out to the form the file path is now cut off at 50 characters.  A check and the table still has the full path.  The table is sql server and a varchar(250) for datatype.  I am lost as to why the path is stopping at 50. 

    Anyone have any thoughts on why this is occurring as I sure don't?

    Hi John,

    Is it really truncated? In that case step through the code with the debugger and see where it is truncated.

    Or are there only (about?) 50 characters displayed on the form because the control width is too narrow to display the full path?

    Imb.

    Tuesday, September 15, 2015 7:42 AM
  • like IMB-hb - I am assuming you have sanity checked and widened that text box control to be really big so you are sure that it really is 50 char count ......

    One cannot define the character count as a property of that text box in the form - so one would look at the record source of the form - the query or view or property of the table that is the record source......


    Tuesday, September 15, 2015 12:48 PM
  • Are there spaces in the pathname?  Could the text perhaps be wrapping within the textbox?
    Tuesday, September 15, 2015 12:58 PM
  • Thanks all for responding.  I traced my way thru from updating the field in the table back to the field on the form.  I updated the field with the full path (way more than 50).  Went to the table and full path was there.  Went to the view in sql server that fills the listbox on the form.  The full path was there.  Looked at the linked view in Access.  The full path stopped at 50.  Used the linked table manager to update, still stopping at 50.  Deleted the view, relinked, reupdated with link manager, still stopping at 50.  Went into the design of the linked view in Access and it shows the field as Short Text with a length of 50.  Can't change the design in Access, it has to be in sql server.  The view is showing full path.  There is another field that is also a varchar(250) and that is not being stopped at 50.  Looked at it and it is Short Text with a field size of 250. So, I went back to the view in sql server and ran it and the full path was there.  Hovered over the field in design and it showed varchar(250).  I then unclicked the field and rechecked it to show in the view.  Put it back in order.  Deleted the link, then relinked the view and ran the link manager.  Opened up the view in Access and the full path was there.  Looked at the design and it was Short Text with field size of 250.  Problem solved.  But not sure why the view would not update thru the link manager?  Any way, problem solved so time to move on.

    Again, thank you all for the reply.

    John

    Tuesday, September 15, 2015 2:11 PM
  • Good detective work. Odd that the Access field definition (length) didn't match that of SQL Server...  Perhaps the field length had changed on the server at some point and the Access linked table didn't get updated dynamically. Glad that recreating the linked table turned out to be the fix.

    • Edited by Gary Voth Tuesday, September 15, 2015 2:35 PM
    Tuesday, September 15, 2015 2:34 PM
  • John

    By any chance did you change a column in the view at the table level? Views don't always refresh when table design is changed. If this is the case you should run sp_refreshview 'yourViewName'


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 15, 2015 4:01 PM
  • Yes.  The field was at 50 then went to 250.  But that was days ago.  The dba indicated that the change occurred.  Which is why at first I did not trace thru the course the data takes.  Apparently, the change did not go all the way thru.  I have brought it to their attention.

    John

    Tuesday, September 15, 2015 4:13 PM
  • Views in SQL Server tend to not see table design changes until refreshed. Therefore both SQL Server and Access will see the view with the old column size until both the view is refreshed AND Access relinks to the view.

    Access Will also fail to see changes made to linked tables until the link is refreshed. One of the routines I keep handy is a SQL Server link refresher. Anytime I change a linked object in SQL Server I run the routine to relink everything in the Access side. Usually this is only done early on in the application design stage.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 15, 2015 5:39 PM
  • Bill, thanks for the insight.  I will remember this for next time.

    John

    Tuesday, September 15, 2015 6:55 PM