none
Access Bookmark problem with SQL Server... RRS feed

  • Question

  • I have an older (97?) access app that I'm trying to maintain for a customer.

    All has been working well, until recently.  We have a form that allows us to maintain data.  Upon entering an Inspection Number, the form is filled and everyone is happy.  We have just started to notice that with more recently entered Inspection Numbers, the record isn't being retrieved.   The back end is SQL Server.  On older records the line:

    frm.Bookmark = rs.Bookmark

    populates the form correctly.  On newer records, this is failing, but without any error.  Am I running into some type of limit between Access and SQL Server?

    Wednesday, January 27, 2016 2:46 PM

Answers

  • Thanks for all the responses!

    I think I have resolved this.  I unlinked/relinked the SQL Server table and 'voila', problem solved!

    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:03 AM
    Thursday, January 28, 2016 12:47 PM

All replies

  • I have an older (97?) access app that I'm trying to maintain for a customer.

    All has been working well, until recently.  We have a form that allows us to maintain data.  Upon entering an Inspection Number, the form is filled and everyone is happy.  We have just started to notice that with more recently entered Inspection Numbers, the record isn't being retrieved.   The back end is SQL Server.  On older records the line:

    frm.Bookmark = rs.Bookmark

    populates the form correctly.  On newer records, this is failing, but without any error.  Am I running into some type of limit between Access and SQL Server?

    Wednesday, January 27, 2016 1:12 PM
  • First, I would strongly recomment upgrading this app to a newer version of Access. Version 97 is no longer supported. Do you have error handling in this procedure that is failing? Do you know what rs.Bookmark is giving you in these instances?

    Wednesday, January 27, 2016 4:19 PM
  • Yes, I understand the need to upgrade and will look into it, but...

    I do have error handling and nothing is being thrown.  As mentioned in the original post, this has been working fine.  I have an ID on Inspection Number.  I have tested this for the first 30,000 ID's and everything is wonderful.  However, once I pass a certain threshold, it doesn't work.  To further complicate this, if I first pull up a lower number Insp# (works correctly), and then pull up a higher number insp# - fails on first try, succeeds on second - at which point I'm in the clear.  From that point forward the form behaves as designed.  One more interesting point.  I tested this by creating an Access table, and the form works.  When I move back to SQL Server, the form behaves incorrectly again.

    Wednesday, January 27, 2016 4:29 PM
  • Wednesday, January 27, 2016 4:42 PM
  • >>>From that point forward the form behaves as designed.  One more interesting point.  I tested this by creating an Access table, and the form works.  When I move back to SQL Server, the form behaves incorrectly again.

    According to your description, since Access 97 is no longer supported, it is difficult to reproduce this issue, so I suggest that you could use DoCmd.FindRecord instead of Bookmark or copy the object which contains the troublesome code into a new copy and delete the old one, rename the new one to old name, then check if bug has gone away.

    Thursday, January 28, 2016 6:34 AM
  • Thanks for all the responses!

    I think I have resolved this.  I unlinked/relinked the SQL Server table and 'voila', problem solved!

    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:03 AM
    Thursday, January 28, 2016 12:47 PM
  • >>>I think I have resolved this. I unlink/relink the SQL Server table and 'voila', problem solved!

    I am glad to hear you have resolved your issue, thanks for you sharing solution, that will help other community members resolve same issue.

    Friday, January 29, 2016 8:09 AM