Making Linked Tables Read-Only

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have a typical MS Access database with a bunch of user-
input forms. It is a secured database. Thus, some users
have full edit rights to certain tables when they log into
it. (For purposes of discussion, we'll call this
the "master database")

I want to make it possible for all my users to link to the
master database from their own personal mdb files. Thus,
they could do their personal queries outside of
the "master database". However, when they create links, I
want those links to be "read-only". I do not want them to
have the same rights they would have if they were working
in the "master database". (In making any changes to data,
I want them to work through forms in the "master database"
which often have code to do something when data is added
or edited.)

In the act of opening any mdb file on their desktops users
pass through the workgroup security. When they link to a
table in the "master database", they now inherit all the
rights they get when they log in to the "master
database". How can I prevent this and limit all users to
read-only when they link?
 
Interesting question.
Never tried it.

A quick look in A97 Help shows:
Permissions Property

Sets or returns a value that establishes the permissions for the user or
group identified by the UserName property of a Container or Document object
(Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a Long constant that establishes permissions.
The following tables list the valid constants for the Permissions property
of various DAO objects. Unless otherwise noted, all constants shown in all
tables are valid for Document objects.
The following table lists possible values for Container objects other than
Tables and Databases containers.

Constant Description

dbSecNoAccess The user doesn't have access to the object (not valid for
Document objects).
dbSecFullAccess The user has full access to the object.
dbSecDelete The user can delete the object.
dbSecReadSec The user can read the object's security-related information.
dbSecWriteSec The user can alter access permissions.
dbSecWriteOwner The user can change the Owner property setting.

The following tables lists the possible settings and return values for the
Tables container.

Constant Description

dbSecCreate The user can create new documents (not valid for Document
objects).
dbSecReadDef The user can read the table definition, including column and
index information.
dbSecWriteDef The user can modify or delete the table definition, including
column and index information.
dbSecRetrieveData The user can retrieve data from the Document object.
dbSecInsertData The user can add records.
dbSecReplaceData The user can modify records.
dbSecDeleteData The user can delete records.


The following tables lists the possible settings and return values for the
Databases container.

Constant Description

dbSecDBAdmin The user can replicate a database and change the database
password (not valid for Document objects).
dbSecDBCreate The user can create new databases. This option is valid only
on the Databases container in the workgroup information file (Systen.mdw).
This constant isn't valid for Document objects.
dbSecDBExclusive The user has exclusive access to the database.
dbSecDBOpen The user can open the database.


Remarks

Use this property to establish or determine the type of read/write
permissions the user has for a Container or Document object.
A Document object inherits the permissions for users from its Container
object, provided the Inherit property of the Container object is set for
those users or for a group to which the users belong. By setting a Document
object's Permissions and UserName properties later, you can further refine
the access control behavior of your object.

If you want to set or return permissions for a user that includes
permissions inherited from any groups to which the user belongs, use the
AllPermissions property.


Sample code:
note the last line -->
ctrTables.Permissions = dbSecInsertData _
And dbSecReplaceData And dbSecDeleteData
SetPermissions = True

Change to:
dbSecRetrieveData


Function SetPermissions(strName As String, strPID As String, _
strPassword As String) As Boolean
Dim dbs As Database, ctrTables As Container
Dim wspDefault As Workspace, usrNew As User

On Error GoTo ErrorSetPermissions
' Return reference to default workspace.
Set wspDefault = DBEngine.Workspaces(0)
' Create User object, specifying name, PID, and password.
Set usrNew = wspDefault.CreateUser(strName, strPID, strPassword)
' Append to Users collection of default workspace.

wspDefault.Users.Append usrNew
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Tables container.
Set ctrTables = dbs.Containers!Tables
ctrTables.UserName = usrNew.Name
' Set new user's permissions for tables.
ctrTables.Permissions = dbSecInsertData _
And dbSecReplaceData And dbSecDeleteData
SetPermissions = True

ExitSetPermissions
Set dbs = Nothing
Exit Function

ErrorSetPermissions

MsgBox Err & ": " & Err.Description
SetPermissions = False
Resume ExitSetPermissions
End Function
 
Back
Top