CReating Read-Only Link between tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Microsoft Access 2003. I have a master table in Database #1 and
want to establish a link to this table in database #2. However, I want to
limit the user of database #2 to read-only access of the master table in
database #1. Is this possible?

Stated alternatively, I want the user of database #2 to always have a copy
of the content and structure of the master table in database #1 that
automatically updates itself whenever the content of the master table
changes, but I do not want to expose the master table to inadvertant or
unintended changes or modifications caused by the user of database #2.
 
Hi.
I want to
limit the user of database #2 to read-only access of the master table in
database #1. Is this possible?
Click to expand...

Yes. But you'll have to implement user-level security, which can be a royal
pain. Instead, I would suggest using a non-updateable remote query as the
data source for your forms and reports in database #2.

To do so, import the table structure -- but absolutely no data -- from
database #1 into database #2. For example purposes, we'll name this table
tblSales and database #1 as db1.mdb, which is located in the C:\Test
directory. Next, create a new query and paste the following SQL statement
into the SQL View Pane:

SELECT *
FROM [;DATABASE=C:\Test\db1.mdb].tblSales
UNION
SELECT *
FROM tblSales;

(Replace the name of the table with your table name and the path and file
name with your own for database #1.) Save and run this UNION query. Try to
change records, and you'll find that it's completely read-only. Use this
UNION query as the record source on forms, and the user in database #2 won't
be making any unauthorized changes.

The downside to this is that the query doesn't automatically update changes
in the data source, so if changes are made in the table in database #1 after
the query is run, then the user in database #2 won't see these changes
immediately. The form needs to periodically requery the remote database. To
do so, the following code will get the latest data from the record source, in
this case, tblSales in db1.mdb:

Me.Requery

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Back
Top