Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007
    Location
    Atlanta Georgia
    Posts
    1

    Unanswered: Linked Table error from access 2003 to SQL Server 2000 database

    I am the SQL dba of the SQL database that is in question. My permissions are owner. I recently added 3 new users to an Access mdb file that accesses this database. Whenever they attempt to Update a record they get the following MS Access error message dialog.

    ODBC--update on a linked table'dbo_tblEmployee' failed

    [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER] UPDATE PERMISSION DENIED ON OBJECT 'Employee_DB', owner 'dbo'. (#229)

    I have given each of the user owner level permissions. In addition, I have checked marked each box for INSERT UPDATE AND DELETE for each user (given that they have owner permissions, I thought that this should not be necessary). Unfortunately, they are still unable to update data in the linked table. Does anyone have a suggestion on what to do to resolve this issue.

    Thanks,
    D. Couse

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the SQL Server permissions...usually I will create a new role, giving that specific role permissions to insert, update, and delete (and Execute and DRI). Then I will add the users to that role. Remember that permissions for SQL Server works in a way that is the most restrictive so if a user is in any roles which Deny (or Revoke) them insert, update, or delete, Execute, DRI, that role takes precedence regardless if they are in another role which Grants them permissions to insert, update, or delete (ie. the green check mark.) Also make sure the roles have permissions to Execute (Execute a stored procedure) and DRI (Declarative Referential Integrity) or they may not be able to update.

    Are you using windows authentication or mixed/SQL Server permissions? If you are using SQL Server permissions verses windows authentication this may be giving you a problem (how did you link the tables into MSAccess? - you were prompted for windows authentication or SQL Server in the wizard)

    Also, when you linked the SQL Server tables into the Access mdb file, there is a Save checkmark. Did you check this box when you linked the tables?

    Also check that the user has an ODBC DSN which is "exactly" the same ODBC DSN name you used to link the tables into the mdb file. If the user's ODBC DSN name is slightly different, you will have problems. Every user's ODBC DSN for their computer should be exactly the same as the other computer's ODBC DSN.

    Lastly, make sure there aren't any trigger's against the SQL Server table: tblEmployee which is updating fields in another table where perhaps they don't have permissions (I'm guessing you don't have any triggers against the tblEmployee but you may want to check.)

    You mention that "I recently added 3 new users to an Access mdb file that accesses this database" - does that mean that you also have MSAccess permissions to this database as well as SQL Server permissions? (I'm guessing that all of your tables in the mdb are SQL Server linked tables and the MSAccess permissions aren't a factor.)
    Last edited by pkstormy; 09-07-07 at 23:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •