|
Subject:
|
Read-Only Linked Table
|
|
Posted By:
|
pjm
|
Post Date:
|
8/4/2006 1:47:05 PM
|
I posted this same topic on the Access forum but I thought that it would be more appropriate here. I'm just going to repeat my original message, the replies, and my responses here:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Yes, that's exactly what I want to do! I have a front-end that links to tables in a back-end database (plain and ordinary MS-Access). For various reasons I can't just mark the back-end database as read-only (which would easily solve my problem).
I have created a "SELECT DISTINCT" query to create a read-only link but I'm not happy with this solution for several reasons:
1) (minor) It will show up in the queries list instead of the tables list.
2) (more than minor) Performance hit using this type of query (as a table substitute) versus a simple link.
3) (relatively major) Cannot include any memo fields in the query.
I tried using the following code to create the link (works fine) and then change it's permissions to make it read-only (not so good):
Dim db As database
Dim con As Container
Dim doc As Document
Dim newLink As TableDef
Set db = CurrentDb()
Set newLink = db.CreateTableDef(TableName)
newLink.Connect = ";DATABASE=" & dbPath & dbName
newLink.SourceTableName = TableName
db.TableDefs.Append newLink
Set con = db.Containers("Tables")
Set doc = con.Documents(TableName)
doc.permissions = dbSecRetrieveData
I can still do all sorts of damage to the underlying table (modify fields, delete records, etc.).
What am I missing?
--------------------------------------------------------------------------------
Reply author: mmcdonal
Replied on: 07/24/2006 12:27:05 PM
Depending on the size f the table, you can trasnfer the table to your local database on open, then delete the table and transfer in a new copy every time the user needs to data. This will not make the data read-only locally, but it will not permit the user to modify the data in the real database, and will overwrite any changes they make as they access new functions.
--------------------------------------------------------------------------------
Reply author: pjm
Replied on: 07/25/2006 3:44:24 PM
That is an approach I have considered, but it would tend to be a bear on performance if I want to let the user access the most up-to-date tables. Even if I just copy in all the latest versions of the tables when the database is opened, this would inflate the size the the entire file considerably and would necessitate fiddling around with deleting tables at the end (a macro that runs when you close an Access db?) along with some compaction.
I do have another query-based solution that I can use (but I'm still not entirely satisfied with it). Instead of the "SELECT DISTINCT" I do a total query grouping by all the fields in the remote table. At least this gets around the idea of not being able to include memo fields while preventing the user from editing the remote table.
--------------------------------------------------------------------------------
Reply author: SerranoG
Replied on: 07/25/2006 4:36:48 PM
The form or subform that the table's data is displayed could be rendered "read-only" by setting the form/subform's AllowEdits, AllowDeletions, and AllowAdditions to FALSE. You can also lock any controls that display that table's data.
Grant it that if the person opens the MDB/MDE using the shift key, the table is vulnerable. For that, you need to set security.
--------------------------------------------------------------------------------
Reply author: pjm
Replied on: 07/26/2006 09:19:12 AM
I should have made it clear: this database is completely open to the user (ie. no forms to restrict access to any tables). I'm trying to protect the remote table from being changed when the user simply opens up the table (sans form).
I'd still like to figure out how to properly set the permissions on a linked table to read-only (or why what I did do doesn't seem to work).
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-Phil-
|
|
Reply By:
|
pdham
|
Reply Date:
|
11/25/2006 7:05:00 AM
|
I needed to do the same thing. The problem is that the users effective permissions (as returned by doc.AllPermissions) are a combination of the user's permissions, and the permissions of any groups the user is a member of. For an unsecured Access 2003 database the default user is "Admin", a member of the "Admins" and "Users" groups. The linked db table has access control entries both on the Admin user and the Users group. To change both from VBA use:
doc.UserName = "Admin"
doc.Permissions = dbSecRetrieveData
doc.UserName = "Users"
doc.Permissions = dbSecRetrieveData
Debug.Print "New AllPermissions: " & doc.AllPermissions
|
|
Reply By:
|
pjm
|
Reply Date:
|
12/1/2006 2:37:45 PM
|
Thanks. That is exactly what I needed!
-Phil-
|
|
