Whether you use the conventional built-in linked table manager for changing the path to the linked tables, or the code for automating this task, one problem exists--as it stands, only administrators can change the path to linked tables. Most likely, you will want certain types of end-users to be able to change this path as well. There is code in Section 14 of Microsoft's Security FAQs which allows you to do just this. (The link above will carry you to this section of Microsoft's Security FAQs.)
One particular observation: this code requires you to execute a function for each and every linked table you have in your database, a tedious process. So I tacked on some extra code on the bottom for highly automating this process. (Other code can be found by searching microsoft.public.access.tablesdbdesign dated April 4th through April 6th 2003, thread name Difficulty Refreshing Linked Tables).
Function faq_SetPermissions(strTable As String, strSourceDB As String, strUsrName As String)
' Derived from section 14 of Microsoft's Security FAQs
' This function will set permissions on the source table so that
' you can use the RefreshLink method to reattach tables. It grants
' Read Data permissions on the source table, Open/Run permissions
' on the source database and full permissions in the destination
' database on all tables and queries. You need to be a member of the
' Admins group to run this code.
'
' Parameters:
' strTable
' Name of the table for permissions to be set. Assumes the
' table named the same in both source and destination db.
' strSourceDB
' Fully-qualified name of the source database
' strUsrName
' Name of group or user you want to be able to
' use RefreshLink
'
Dim db As Database
Dim con As Container
Dim doc As Document
Dim ws As Workspace
Set ws = DBEngine.Workspaces(0)
' set default full permissions in destination
' (current) database for new tables
Set db = CurrentDb()
Set con = db.Containers("Tables")
con.userName = strUsrName
con.Permissions = DB_SEC_FULLACCESS
' set full permissions for the linked table
' in the destination database
Set doc = con.Documents(strTable)
doc.userName = strUsrName
doc.Permissions = DB_SEC_FULLACCESS
' Set open database permissions for the source database
Set db = ws.OpenDatabase(strSourceDB)
Set con = db.Containers("Databases")
Set doc = con.Documents("MSysdb")
doc.userName = "Users"
doc.Permissions = doc.Permissions Or DB_SEC_DBOPEN
' Set read data permissions for the base table
Set con = db.Containers("Tables")
Set doc = con.Documents(strTable)
doc.userName = strUsrName
doc.Permissions = doc.Permissions Or DB_SEC_RETRIEVEDATA
End Function
Function givepermissionslrh()
' Executes the above module--derived from Microsoft's Security FAQ--much more quickly
' as the above would have to be re-executed for every table in the database
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblApprovalGuidelines") ' Enter name of your 1st table
For Each tdf In dbs.TableDefs
' Next line specifies--user specific--which tables exist in the table
' database window. I have specified that tblTmpPO and tblTmpInv should be skipped
' since they are "local" tables and have very limited usage
If tdf.Name Like "tbl*" And tdf.Name <> "tblTmpInv" And tdf.Name <> "tblTmpPO" Then
Call faq_SetPermissions(tdf.Name, "C:\db\po_invoices_be.mdb", "Full Data Users")
End If
Next tdf
End Function
|