• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Can't navigate records in ADP form when Recordsource is filtered (error 2105)

I have a form in an Access 2003 ADP that is bound to a single table in a SQL 2005 database. The form's OnOpen event filters the form's recordsource. When I try to use the form on a login that has only Select/References rights in the underlying table, it doesn't let me navigate from one record to another, and acts as if the user is trying some kind of illegal update operation:
- Attempting to go to previous/next record triggers error 2105 ("You can't go to the specified record"); however, when I use the connection's Errors collection to get more info, there are no errors in the collection.
- When you close the form, Access displays an error dialog: "The UPDATE permission was denied on the object '(name of table bound to form)', database ... etc." - when in fact no update was attempted.
- This is followed by another Access dialog, "You can't save this record at this time... do you want to close this database object anyway"

If I remove the filtering, the problem goes away! The form behaves as one would expect, allowing the read-only user to navigate and view but not update. It appears that Access interprets a filter as an attempt to "update" the table, making the form unusable. I've tried creating the filter both by setting "me.filter" in the form's code, and by changing "me.recordsource" - the result is exactly the same either way. I need to be able to dynamically filter the form's data - any suggestions would be very appreciated. Thanks!
  • 2
1 Solution

Access does some things "behind the scene" you cannot debug using Access.
I would recommend to use the SQL Server Profiler to see what exactly happens in the background between Access and the server. Access often uses some own stored procedures to handle things, especially if you have selected a record source with the dropdown field in the form properties. To avoid this, use an own view or stored procedure and don't select it from the dropdown, enter an own SQL statement like "SELECT x,y,z FROM mytable WHERE..." in the record source or in case of stored procedure "EXEC mysp @myparam=...". On this way Access sends the command directly without any change to the server. Moreover it can help to disable the "fetch default" property and set the recordset to "snapshot" instead of "updateable snapshot".


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using the ServerFilter property?

Are you 100% certain that you've properly set a Primary Key on the table(s)?

I would agree with Christian regarding the use of a Stored Proc to do this.
CESD123Author Commented:
Thank you both for your contributions. I think the key was the RecordsetType; I tried changing it from "updateable snapshot" to "snaphot" in the form's Load event, and that seems to mostly fix it, along with setting "Fetch Defaults" to No.

In the future I may try making the recordsource a stored procedure (it already was a SQL SELECT statement); hopefully that may simplify some issues around users' rights in the form.
CESD123Author Commented:
Thanks for very clear and useful advice; this is an area not well covered in existing information about Access ADP's.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now