Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Is it possible to make linked tables read-only?

    Access 2000 file format with linked tables to SQL Server via ODBC.

    The users permissions on the SQL server are elevated due to inheritance from a 3rd-party application. However they are using Access linked tables to perform ad-hoc querying.

    I am worried about the users ability to modify the data in this manner (all too easily!) and am wondering if there's any way to set the linked tables to read-only?

    I'm aiming for a sticking plaster for now as a more permanent security solution is in the pipeline.
    George
    Home | Blog

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What is it possible to modify on the server side and on the Access database side without breaking the 3rd party application?
    Have a nice day!

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Access on the 3rd party app is via a Windows auth'd AD group which has read-write perms on the SQL database. Therefore these permissions are inherited across ODBC.

    I have pretty free rein on the Access side.
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can drop the primary key index on the table(s) which makes it (them) read-only:
    Code:
        CurrentDb.Execute "DROP INDEX PK_Tbl_Orders ON dbo_Tbl_Orders;"
    To know the name of the index, use:
    Code:
    Sub EnumIndex()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim idx As DAO.Index
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("dbo_Tbl_Orders")
        For Each idx In tdf.Indexes
            If idx.Primary = True Or idx.Unique = True Then
                Debug.Print idx.Name
            End If
        Next idx
        
    End Sub
    Note: This will not remove the actual primary key on the server table.
    Last edited by Sinndho; 05-02-13 at 17:23. Reason: Note added.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or consider using a read only view
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers Sinndho, I will have a toy with that method. Should work as long as the users don't relink the tables!

    Healdem; can you elaborate?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So I worked out how to achieve what I wanted here!

    I created a view for each object on the SQL server, within a separate schema, with the same object name.
    I then deleted the connections in Access and replaced them with the new views. A simple rename of the link in Access to make the replacement complete.

    Because Access can't find any unique key information on the view the ability to perform CRUD operations was no longer available!



    Should have thought of this to be honest as I faced a similar problem recently, but the other way round (my linked views were read-only when I didn't want them to be!).
    George
    Home | Blog

Posting Permissions

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