Access Bookmark problem with SQL Server...

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?

Answer 1

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?

Answer 2

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?

Answer 3

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.

Answer 4

There are many possibilities. Look here for some ideas:

http://stackoverflow.com/questions/5420835/access-2007-sql-2008-bookmark-operation-stopped-working

Answer 5

>>>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.

Answer 6

Thanks for all the responses!

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

Answer 7

>>>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.