linked table read only

Question

I have an access 2003 (mdb) database on housing projects I update for our local planning office on a quarterly basis. Other folks in our office could benefit from seeing the same data. I thought the easiest way was to give them a separate access database (whether version 2003 or 2007), linking from it to the source table I update. However, to prevent them from inadvertently changing the source data when viewing, I would like to make the linked table read only.

Any cues on how to restrict access permissions for linked tables for Access 2007?

Answer 1

You could store the db file in a shared folder where the other users have read-only permission. Then they should still be able to view, but not change, the data in the linked table.

If that suggestion is not satisfactory, you can use a query to limit them to read-only access. In the database you give the users, don't include a link to the source table in your other database. Instead give them a query which finds the source table without a link ... in the form of FROM TableName IN '[path to db file]'

Here is a tested example:

SELECT
    u.UserID,
    u.FName,
    u.LName,
    u.AccessLevelID
FROM tblUser AS u IN 'C:\share\Access\loginexample.mdb';

However, that query result could still be editable. You can make the column values read-only with field expressions in place of the actual field values.

SELECT
    u.UserID + 0 AS UserID,
    u.FName + '' AS FName,
    u.LName + '' AS LName,
    u.AccessLevelID + 0 AS AccessLevelID
FROM tblUser AS u IN 'C:\share\Access\loginexample.mdb';

Those were simple transformations to make the values read-only. But you can use other techniques as appropriate. For example, if the table includes a Date/Time field, you could use CStr() or Format().

CStr(date_field) AS date_field_as_text
Format(date_field, 'yyyy-mm-dd hh:nn:ss ampm') AS date_field_as_text

Note I used an alias which was distinct from the field name with those functions. In some cases, Access will complain about a "recursive alias" when you attempt to re-use the field name as the alias. But just test your alias choices in the query designer to quickly find out which are acceptable.