Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Strange behavior on a linked SQL Server table in Access 2007

    I have a SQL Server (2005) table linked to an Access 2007 database that will intermittently not allow me to run an update query on it. The connection string is as follows:

    ODBC;DRIVER=SQL Server;SERVER=[server];APP=2007 Microsoft Office system;DATABASE=[Database];Trusted_Connection=Yes;TABLE=dbo.[Table]

    This is the message I get:
    The UPDATE permission was denied on the object 'Table', database 'Database', schema 'dbo'.

    At first I thought it had to do with end-user permissions, but that idea died when I got the same error running it as myself (db_owner).

    The strange thing is I can open up the linked table in datasheet view and manually edit data in the table (and then immediately change it back). After doing so, I can then run the update query successfully. The SQL table does have a primary key (clustered, unique).

    Has anyone experienced this behavior?
    Last edited by dare2be; 05-04-10 at 17:54.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by dare2be View Post
    I have a SQL Server (2005) table linked to an Access 2007 database that will intermittently not allow me to run an update query on it. The connection string is as follows:

    ODBC;DRIVER=SQL Server;SERVER=[server];APP=2007 Microsoft Office system;DATABASE=[Database];Trusted_Connection=Yes;TABLE=dbo.[Table]

    This is the message I get:
    The UPDATE permission was denied on the object 'Table', database 'Database', schema 'dbo'.

    At first I thought it had to do with end-user permissions, but that idea died when I got the same error running it as myself (db_owner).

    The strange thing is I can open up the linked table in datasheet view and manually edit data in the table (and then immediately change it back). After doing so, I can then run the update query successfully. The SQL table does have a primary key (clustered, unique).

    Has anyone experienced this behavior?
    This may help:

    Optimizing Microsoft Office Access Applications Linked to SQL Server
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    3
    Thanks for your quick reply. Yes, this helped me decide to port the batch update of the SQL Server table to the server side, since the SQL server table destination was relatively large (500k records). I created a new table in SQL Server to house the records to be updated, created delete/append queries to that table, and then wrote a stored procedure to do the update between the two SQL tables. I then created a pass-thru query in Access to execute that stored proc.

    However, now when I run the pass-thru query, I get an "Execute permission denied" message, until I manually open one of the linked SQL tables in datasheet view and edit a record. Then the pass-thru query works.

    It seems that there is some initial handshaking between Access and SQL Server that isn't occurring properly until I manually connect (and edit) one of the linked tables.

  4. #4
    Join Date
    May 2010
    Posts
    3

    Partial explanation

    Running SQL Profiler revealed something interesting. One of the SQL views is linked using a SQL server account with read-only access to the database. Even though the pass-thru query is set to use a Trusted Connection, instead of using the Windows account of the logged in user, it is using the SQL Server read-only account. After I force the usage of the Windows account by manually editing one of the linked tables that are attached with "Trusted Connection", does the pass-thru query work properly.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes there is some hand-shaking type possibly going on. MSAccess usually recognizes a SQL Server linked table with the prefix dbo_ so that might be part of the issue.

    For example, I originally create all my MSAccess tables (before upsizing to SQL Server) as dbo_TableX.

    Then after upsizing to SQL Server, I rename the tables on SQL Server without the dbo_ and link them into MSAccess (which will automatically put in dbo_ for the table name) and I then don't need to change any code based on dbo_TableX (or rename the table).

    I'm guessing (since you mentioned dbo_) that this might be part of the translation issue. ODBC seems to want to put dbo_ in front of SQL Server linked tables into MSAccess. The fact that you said it couldn't recoginize 'dbo' seems to oddly be a coincidence in this case.
    Last edited by pkstormy; 05-11-10 at 00:52.
    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
  •