|
Subject:
|
Read-Only Linked Table
|
|
Posted By:
|
pjm
|
Post Date:
|
7/20/2006 10:07:39 AM
|
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 sevberal 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?
-Phil-
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
7/24/2006 11:27:05 AM
|
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.
Does that help?
mmcdonal
|
|
Reply By:
|
pjm
|
Reply Date:
|
7/25/2006 2: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.
Thanks for the suggestion anyhow.
-Phil-
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
7/25/2006 3: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.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
pjm
|
Reply Date:
|
7/26/2006 8: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).
I do appreciate all the ideas anyhow.
-Phil-
|
|
Reply By:
|
pjm
|
Reply Date:
|
8/18/2006 1:35:28 PM
|
I wanted to copy this topic to the Access VBA forum because a) I thought that would be more appropriate and b) I didn't want to keep flipping back & forth between forums. Since there doesn't seem to be any way to just move the topic to the other forum, I copied most of the thread to one message in a new topic in the Access VBA forum. If you do have a reply to this topic I'd appreciate it if you could respond to the same topic in the Access VBA forum.
-Phil-
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/21/2006 7:42:15 AM
|
Move the back end to SQL.
mmcdonal
|
