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

Late to the game with this answer, but this is something that MS-Access developers occasionally need to do...

...And it's arcane, because we're going to use a read-only query with an internally-defined connection string, and the 'Query Properties' window doesn't quite do what you would expect it to do.

Actually, I don't think that window does whatever the Microsoft developer who implemented the 'Source Connection String' property expects it to do, either.

But, arcane or not, here's how to do something that works like a read-only linked table from another MS-Access database:

1. First, create a new query on a local table. It doesn't matter what table or what fields, you're discarding the SQL anyway.
2. Right-Click in the query window's title bar and select 'SQL View'
3. Paste this SQL in, overwriting whatever was there:

SELECT *
  FROM tblCustomer 
    IN "" [MS Access;PWD=WTF_En_Clair;DATABASE=\\MyServer\MyShare$\Subfolder\MyDB.accdb];

Don't miss out those double-quotes in front of the connection string: they aren't put in there for you by the built-in properties dialogue when you paste in a connection string, and you really do need them.

You're not yet done: it's still read-write:

4. Right-Click in the query window title bar, and select 'Design View'
5. Right-click in the background area of the upper pane - the MDI window where tables appear - and select 'Properties..."
6. You'll probably get a 'Property Sheet' popup with two fields: 'Alias and Source' - this is the table's property sheet, and you want the query's property sheet...
7. ...So left-click in the MDI background of the upper pane again to get the full property sheet for the query.
8. Set the 'Recordset Type' property to 'Snapshot'
9. Right-click in the query window title bar and hit 'Save'.
10. You're done with the query window. Don't do anything else here, just close the window. .

You can rename the query to the table name, or not: it'll still work in any query as if it was a table, and you might just save some confusion if you give it a name that makes it clear that this isn't actually a table. This matters in any code that expects a DAO TableDef object, and that includes any code that re-links external tables for you.

You'll notice your connection string (plain text password and all) in the query properties window under 'Source Connection String'. Seriously, don't edit it: if you're lucky, it'll just replace your two double-quotes in the SQL with a pair of single quotes, and the query will still work. But you'll probably lose those quotes if you paste in a new connection string, and that'll break the query; and none of the error messages and help pages will tell you that you need these magic quote marks in the raw SQL.

I suspect that there are other undocumented 'gotcha' traps in queries to external objects: if you pass this hack on to your colleagues, I strongly advise you to pass on the warning 'You're done with the query window. Don't do anything else here' because this type of trap can waste hours of your time and theirs.

Also: be sure to document what you did: most of the tools for reconnecting linked tables won't pick up a query with an external source, and that's a bug waiting to bite whoever tries to hop between 'DEV', Testing' and 'Production' databases.

Answer 2

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.