How to make all access to linked table read-only

[Bob Alston]

Anyone know a way to make all access to a linked table, in another
Access MDB, read only?

I really don't want all the hassle of implementing full access security.

I can't do this at the server file system because in some front ends the
user needs update access.

I want to give users access to the data for reporting with their own
queries and reports. I just don't want to take a chance on them
updating the data via such tools.

Bob

[Wayne Morgan]

While this won't stop them if they get access to the database window, you
could make the queries "Snapshots". Snapshots are read only and any
subsequent object based on that query will also be read only for data that
comes from that query. Also, reports aren't a problem, there is no editing
data that the user can do in a report. The problem would come it if you give
them access to a form or query (or the table itself) that is read/write.

--
Wayne Morgan
MS Access MVP

"Bob Alston" <tulsaalst...@cox.net> wrote in message
news:W%dGe.53575$4o.43523@fed1read06...

[Bob Alston]

Wayne Morgan wrote:
> While this won't stop them if they get access to the database window, you
> could make the queries "Snapshots". Snapshots are read only and any
> subsequent object based on that query will also be read only for data that
> comes from that query. Also, reports aren't a problem, there is no editing
> data that the user can do in a report. The problem would come it if you give
> them access to a form or query (or the table itself) that is read/write.
>

GREAT.

In the interim read about a way to relate the table to a table in a
read-only db and use the resultant query.

Your's is cleaner. And I understand I can hide the linked table entry
(but not sure how to unhide it to see it again).

Bob

[Bob Alston]

OK but how do I specify that a query is to be/produce a snapshot instead
of a dynaset?

Bob

[Bob Alston]

I trust you didn't mean create a snapshot of the data via using a
make-table query!!!! Ugly.

Bob

[David W. Fenton]

"Wayne Morgan" <comprev_gothro...@hotmail.com> wrote in
news:OyeGe.2068$oY....@newssvr25.news.prodigy.net:

> While this won't stop them if they get access to the database
> window, you could make the queries "Snapshots". Snapshots are read
> only and any subsequent object based on that query will also be
> read only for data that comes from that query. Also, reports
> aren't a problem, there is no editing data that the user can do in
> a report. The problem would come it if you give them access to a
> form or query (or the table itself) that is read/write.

Why not skip linked tables and assign the recordsources using
connect strings, like:

SELECT ...
FROM MyTable IN '\\Server\databases\Data.mdb'
WHERE ...

Then set the recordset type for the form to SNAPSHOT.

Again, absent user-level security, the end user could still change
the recordsource and the recordset type property at runtime, but
this would surely slow them down substantially.

The other advantage of this is that you could alter the recordset
type property at runtime according to who the user is, so that you
wouldn't have to have completely different front ends for the
read-only and editing-allowed users.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bob Alston]

I don't want to use a form. I suspect the users will work from queries,
do cross tabulations and occasionally reports. So I really need to
protect the table or hide the table and protect the underlying query.

Bob

[Br@dley]

By now you could have easily setup jet security to limit write access to
your tables. It's there for a reason so why not use it?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Bob Alston]

I have used it and find it adds a layer of complexity to things. I try
to avoid if I can.

So far I found the one very simple approach, noted above.
Create a new MDB with one table with one field defined. Using the
operating system, make the mdb read only.
In the user's database, link to the real data and to the r/o database.
CReate a new query with both the real data and r/o table. Select all
fields from the real data. No need to select any data from the r/o
table or make any relation.
Save the query.
Hide the two linked table definitions.
All the user sees is the query which has the desired data but read-only.
Here is the thread where I learned how to do this:
http://groups-beta.google.com/group/comp.databases.ms-access/browse_thread/thread/44ee4c78ca532cbe/24ef78d347511c73?q=make+table+%22read+only%22&rnum=11&hl=en#24ef78d347511c73

But am still open to anything even easier than that.

Bob

[Wayne Morgan]

To unhide it to see it again, go to Tools|Options|View tab and check the box
next to Hidden Objects. Don't hide the table using code, there is a bug that
can cause you problems.

--
Wayne Morgan
MS Access MVP

"Bob Alston" <tulsaalst...@cox.net> wrote in message

news:NKeGe.53577$4o.5708@fed1read06...

[Wayne Morgan]

Open the query in design view. Right click the background area where the
tables go and choose Properties. Set Recordset Type to Snapshot.

--
Wayne Morgan
MS Access MVP

"Bob Alston" <tulsaalst...@cox.net> wrote in message

news:fReGe.53578$4o.45887@fed1read06...

[Bob Alston]

Wayne Morgan wrote:
> Open the query in design view. Right click the background area where the
> tables go and choose Properties. Set Recordset Type to Snapshot.
>

Very slick. Thank you!!!

Bob

[David W. Fenton]

Bob Alston <tulsaalst...@cox.net> wrote in

news:btfGe.53580$4o.386@fed1read06:

> I don't want to use a form. I suspect the users will work from
> queries, do cross tabulations and occasionally reports. So I
> really need to protect the table or hide the table and protect the
> underlying query.

Your aims are mutually contradictory.

It can be done with Jet user-level security, but that is crackable.

[Bob Alston]

David W. Fenton wrote:
> Bob Alston <tulsaalst...@cox.net> wrote in
> news:btfGe.53580$4o.386@fed1read06:
>
>
>>I don't want to use a form. I suspect the users will work from
>>queries, do cross tabulations and occasionally reports. So I
>>really need to protect the table or hide the table and protect the
>>underlying query.
>
>
> Your aims are mutually contradictory.
>
> It can be done with Jet user-level security, but that is crackable.
>

As I said earlier it can be done with jet security, but I feel that is
overkill.

I do not feel that my aims are mutually contradictory and it doesn't
bother me if you see it that way. I didn't ask for help on my aims just
on how to do it.
I just wanted to find out how to provide a table or query that a user
would not be able to update. I found that such was quite easy to
accomplish. I have no wish to make this super secure, just avoid
careless errors.

Bob

[David W. Fenton]

Bob Alston <tulsaalst...@cox.net> wrote in

news:LHgGe.53581$4o.23649@fed1read06:

> I have used it and find it adds a layer of complexity to things.
> I try to avoid if I can.
>
> So far I found the one very simple approach, noted above.
> Create a new MDB with one table with one field defined. Using the
> operating system, make the mdb read only.
> In the user's database, link to the real data and to the r/o
> database. CReate a new query with both the real data and r/o
> table. Select all fields from the real data. No need to select
> any data from the r/o table or make any relation.
> Save the query.
> Hide the two linked table definitions.
> All the user sees is the query which has the desired data but
> read-only. Here is the thread where I learned how to do this:
> http://groups-beta.google.com/group/comp.databases.ms-access/browse
> _thread/thread/44ee4c78ca532cbe/24ef78d347511c73?q=make+table+%22re
> ad+only%22&rnum=11&hl=en#24ef78d347511c73
>
> But am still open to anything even easier than that.

Without user-level security, the user could edit that and create a
query that is *not* read-only.

Second, how are you "hiding" the linked tables? By marking them
"hidden?" That's laughable. The only thing the user has to do to get
around that is to go to TOOLS | OPTIONS and turn on display of
hidden objects.

You can't do what you want to do without Jet user-level security,
which you reject as too complicated.

You've rejected every solution that actually accomplishes the goals
you have set out, and yet, you seem to accept a method that is only
cosmetically restrictive for the end users.

You can't do what you want to do without some level of Jet
user-level security.

[David W. Fenton]

Bob Alston <tulsaalst...@cox.net> wrote in

news:nihGe.53583$4o.15755@fed1read06:

Without Jet user-level security, the end user can still edit this
query and set it to Dynaset, if they are smart enough to do so.

You seem to be choosing methods that depend on the ignorance of your
users in order to restrict their access to your data. That doesn't
sound very prudent to me.

[Robert]

This thread is helpful. I have a situation where I am Linking a bunch of
different DB Tables to a laptop for work. I want all the DB's on the laptop
to be Read Only but I can only find info on how to make the Forms Read Only.
Can I lock down all the linked Tables on my Laptop?

Thanks,

Robert

"Bob Alston" <tulsaalst...@cox.net> wrote in message

news:NKeGe.53577$4o.5708@fed1read06...

[Robert]

This could be usefull. I've got a situation where I'm using Linked copies
of a bunch of tables on a laptop and I want all the laptop Linked Tables to
be Read-Only. Is there a simple way to do that? All I seem to be able to
find in the Access Help is info on how to make Forms Read-Only.

Thanks,

Robert

"Bob Alston" <tulsaalst...@cox.net> wrote in message

news:NKeGe.53577$4o.5708@fed1read06...

[David W. Fenton]

Bob Alston <tulsaalst...@cox.net> wrote in

news:_KyGe.53639$4o.20510@fed1read06:

And it's *very* easy for an end user to undo your work that makes it
read-only.

You've accomplished nothing except changing the default behavior.

[Br@dley]

Wayne Morgan <comprev_gothro...@hotmail.com> wrote:
> To unhide it to see it again, go to Tools|Options|View tab and check
> the box next to Hidden Objects. Don't hide the table using code,
> there is a bug that can cause you problems.

Ever tried hiding them in code? I think you can nly unhide them via code
which is sometimes useful. (I haven't done it myself though).

> "Bob Alston" <tulsaalst...@cox.net> wrote in message
> news:NKeGe.53577$4o.5708@fed1read06...
>> Wayne Morgan wrote:
>>> While this won't stop them if they get access to the database
>>> window, you could make the queries "Snapshots". Snapshots are read
>>> only and any subsequent object based on that query will also be
>>> read only for data that comes from that query. Also, reports aren't
>>> a problem, there is no editing data that the user can do in a
>>> report. The problem would come it if you give them access to a form
>>> or query (or the table itself) that is read/write.
>>>
>> GREAT.
>>
>> In the interim read about a way to relate the table to a table in a
>> read-only db and use the resultant query.
>>
>> Your's is cleaner. And I understand I can hide the linked table
>> entry (but not sure how to unhide it to see it again).
>>
>> Bob

--

[yulanyou]

Bob Alston 写道：

[Wayne Morgan]

You are correct, but read this first.

http://www.mvps.org/access/bugs/bugs0036.htm

--
Wayne Morgan
MS Access MVP

"Br@dley" <n0m...@4u.com> wrote in message
news:s3dHe.68125$oJ.1...@news-server.bigpond.net.au...

[RoyVidar]

Wayne Morgan wrote in message
<krmHe.3009$1g5....@newssvr33.news.prodigy.com> :

> You are correct, but read this first.
>
> http://www.mvps.org/access/bugs/bugs0036.htm

Does this apply to the .SetHiddenAttribute method of the
Application object, too? Available starting with the 2000
version, I think.

application.sethiddenattribute actable, "mytable", true

--
Roy-Vidar

[Wayne Morgan]

I don't know. Actually, it appears that in newer versions of Access that
this bug may be fixed (whenever I've tried it the bug hasn't happened), but
there has been no official word that I'm aware of that the bug was fixed.

--
Wayne Morgan
MS Access MVP

"RoyVidar" <roy_vid...@yahoo.no> wrote in message
news:mn.0b5a7d584...@yahoo.no...