Control Rights to Linked Tables in MS-Access

I'm afraid I already know the answer to this, but I'm checking with the community in case there's something I don't know about....

Is it possible to have the MS Access table linking feature control rights开发者_运维百科 to linked tables? Secifically, can FE.accdb have read/write privileges on BE_A.accdb, but read-only links to BE_B.accdb (without making BE_B.accdb entirely R/O)?

Sort of.

Quoting David Fenton from a post earlier this year on another forum:

One way would be to remove the linked tables, and create queries to replace them (you could use the same name for the query as the corresponding linked tables), and use a connect string in the query and set the recordset type to snapshot, which will be read-only by default.

I just recently had occasion to do this myself. I asked a related question about setting the querydef recordset type to snapshot via VBA here.

You can adapt the following code to do what you need:

Sub ReadOnlyLink(MDBPath As String, TblName As String, SrcTblName As String)
Dim q As DAO.QueryDef
    Set q = CurrentDb.CreateQueryDef(TblName, "SELECT * FROM " & SrcTblName & _
                                              " IN """ & MDBPath & """")
    q.Properties.Append q.CreateProperty("RecordsetType", dbByte, 2)
End Sub

This isn't really an answer, but it's too long for comments. Thoughts sparked by the answer I accepted.

It seems that it should be possible to use this for user-level "security":

• Set up a BE
• Set up a distributable FE with deliberately broken links
• in the FE startup, get user name (API calls)
• Run user name through a Select Case, re-linking as either real table or querydef as appropriate. Might be even better to do it with a user name -> role lookup before the Select.

Hmmm. Have to think about that some more.... Not bulletproof, but not really intended to be. More a way of dividing responsibility for updates to the users that are the respective domain experts. Don't know if it's really justified.