Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 4th, 2006, 01:47 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default Read-Only Linked Table

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-
__________________
      -Phil-
 
Old November 25th, 2006, 08:05 AM
Registered User
 
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old December 1st, 2006, 03:37 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks. That is exactly what I needed!

-Phil-





Similar Threads
Thread Thread Starter Forum Replies Last Post
Read-Only Linked Table pjm Access 6 August 21st, 2006 07:42 AM
Can a Make Table Query produce a Linked table? kronik Access 5 May 16th, 2006 06:17 AM
Linked Table sdilucca Access 1 February 24th, 2006 07:29 AM
Read Linked tables in Access from VB code meggan VB Databases Basics 3 March 4th, 2005 11:26 AM
linked table Tasha Access VBA 1 August 6th, 2004 03:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.