I am migrating the tables for an application from Access 2003 to SQL Server
2000. The data in these tables is to be maintained by one department, but
the intent is for the rest of the company to have Read Only access. This is
being accomplished using the Window's Network logons and SQL user groups.

While testing, one of the "Read Only" users changed some data on a form...
no problem until he tried to navigate to another record, at which point the
form_error subroutine was called and he was presented with my customized
message box informing him that he was not authorized to update the data
(Response is set to acDataErrConinue). After clicking 'OK' he is then
presented with an application error message containing the following text and
Access hangs up:

ODBC--update on linked table 'tbl_Input' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE permission denied
on object 'tbl_Input', database "Manufacturing', owner 'dbo'. (#229)


Is there anyway to ignore the attempt to change data and simply allow the
user to navigate through the records without hanging up the application?

How is he navigating to another record? Is he using the form's navigation
buttons? If so, you might try adding command buttons that do the navigation
and then trap the errors on those routines. I think you'll have more
flexibility in how you deal with the error.

HTH,
Barry

"Peter C" wrote:

> I am migrating the tables for an application from Access 2003 to SQL Server
> 2000. The data in these tables is to be maintained by one department, but
> the intent is for the rest of the company to have Read Only access. This is
> being accomplished using the Window's Network logons and SQL user groups.
>
> While testing, one of the "Read Only" users changed some data on a form...
> no problem until he tried to navigate to another record, at which point the
> form_error subroutine was called and he was presented with my customized
> message box informing him that he was not authorized to update the data
> (Response is set to acDataErrConinue). After clicking 'OK' he is then
> presented with an application error message containing the following text and
> Access hangs up:
>
> ODBC--update on linked table 'tbl_Input' failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE permission denied
> on object 'tbl_Input', database "Manufacturing', owner 'dbo'. (#229)
>
>
> Is there anyway to ignore the attempt to change data and simply allow the
> user to navigate through the records without hanging up the application?
>

The user was using the navigation buttons at the bottom of the form. I agree
that trapping this error in this way would give me more flexibility, but I
still don't know what to do to get rid of/avoid/back out of the problem.

One thing that I stumbled across that hold some promise is the idea of
changing the form.RecordsetType property to 'Snapshot' from 'Dynaset' , or
change the form.AllowEdits property to NO, based upon the permissions of the
user... But I don't know how to obtain the permissions of the user. Any
ideas on that would be helpful.

"Barry Gilbert" wrote:

> How is he navigating to another record? Is he using the form's navigation
> buttons? If so, you might try adding command buttons that do the navigation
> and then trap the errors on those routines. I think you'll have more
> flexibility in how you deal with the error.
>
> HTH,
> Barry
>
> "Peter C" wrote:
>
> > I am migrating the tables for an application from Access 2003 to SQL Server
> > 2000. The data in these tables is to be maintained by one department, but
> > the intent is for the rest of the company to have Read Only access. This is
> > being accomplished using the Window's Network logons and SQL user groups.
> >
> > While testing, one of the "Read Only" users changed some data on a form...
> > no problem until he tried to navigate to another record, at which point the
> > form_error subroutine was called and he was presented with my customized
> > message box informing him that he was not authorized to update the data
> > (Response is set to acDataErrConinue). After clicking 'OK' he is then
> > presented with an application error message containing the following text and
> > Access hangs up:
> >
> > ODBC--update on linked table 'tbl_Input' failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE permission denied
> > on object 'tbl_Input', database "Manufacturing', owner 'dbo'. (#229)
> >
> >
> > Is there anyway to ignore the attempt to change data and simply allow the
> > user to navigate through the records without hanging up the application?
> >

Changing to snapshot would prevent changes, but you might still get a gnarly
error. I've never been a big fan of the built-in navigation buttons. I like
the flexibility I get from coding my own navigation, like being able to gray
out a button if they are at the last record.
There are several system stored procs that you can use to evaluate SQL
permissions. Check BOL.

Barry

"Peter C" wrote:

> The user was using the navigation buttons at the bottom of the form. I agree
> that trapping this error in this way would give me more flexibility, but I
> still don't know what to do to get rid of/avoid/back out of the problem.
>
> One thing that I stumbled across that hold some promise is the idea of
> changing the form.RecordsetType property to 'Snapshot' from 'Dynaset' , or
> change the form.AllowEdits property to NO, based upon the permissions of the
> user... But I don't know how to obtain the permissions of the user. Any
> ideas on that would be helpful.
>
> "Barry Gilbert" wrote:
>
> > How is he navigating to another record? Is he using the form's navigation
> > buttons? If so, you might try adding command buttons that do the navigation
> > and then trap the errors on those routines. I think you'll have more
> > flexibility in how you deal with the error.
> >
> > HTH,
> > Barry
> >
> > "Peter C" wrote:
> >
> > > I am migrating the tables for an application from Access 2003 to SQL Server
> > > 2000. The data in these tables is to be maintained by one department, but
> > > the intent is for the rest of the company to have Read Only access. This is
> > > being accomplished using the Window's Network logons and SQL user groups.
> > >
> > > While testing, one of the "Read Only" users changed some data on a form...
> > > no problem until he tried to navigate to another record, at which point the
> > > form_error subroutine was called and he was presented with my customized
> > > message box informing him that he was not authorized to update the data
> > > (Response is set to acDataErrConinue). After clicking 'OK' he is then
> > > presented with an application error message containing the following text and
> > > Access hangs up:
> > >
> > > ODBC--update on linked table 'tbl_Input' failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE permission denied
> > > on object 'tbl_Input', database "Manufacturing', owner 'dbo'. (#229)
> > >
> > >
> > > Is there anyway to ignore the attempt to change data and simply allow the
> > > user to navigate through the records without hanging up the application?
> > >

Peter C <Peter (E-Mail Removed)> wrote:

>I am migrating the tables for an application from Access 2003 to SQL Server
>2000. The data in these tables is to be maintained by one department, but
>the intent is for the rest of the company to have Read Only access. This is
>being accomplished using the Window's Network logons and SQL user groups.
>
>While testing, one of the "Read Only" users changed some data on a form...
>no problem until he tried to navigate to another record, at which point the
>form_error subroutine was called and he was presented with my customized
>message box informing him that he was not authorized to update the data
>(Response is set to acDataErrConinue). After clicking 'OK' he is then
>presented with an application error message containing the following text and
>Access hangs up:
>
> ODBC--update on linked table 'tbl_Input' failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE permission denied
>on object 'tbl_Input', database "Manufacturing', owner 'dbo'. (#229)
>
>
>Is there anyway to ignore the attempt to change data and simply allow the
>user to navigate through the records without hanging up the application?


Rather than trying to trap an error when users edit a
record they're not supposed to edit, I would set AllowEdits
in the Load event (for all records) or the Current event for
specific records. If you also have one or more unbound
controls that are used for something else, then set each
bound control's Lock property.

--
Marsh
MVP [MS Access]