How to make all access to linked table read-only

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

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:W%dGe.53575$4o.43523@fed1read06...[color=blue]
> 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[/color]

re: How to make all access to linked table read-only

Wayne Morgan wrote:[color=blue]
> 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.
>[/color]
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

re: How to make all access to linked table read-only

Bob Alston wrote:[color=blue]
> Wayne Morgan wrote:
>[color=green]
>> 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.
>>[/color]
> 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[/color]
OK but how do I specify that a query is to be/produce a snapshot instead
of a dynaset?

Bob

re: How to make all access to linked table read-only

Bob Alston wrote:[color=blue]
> Wayne Morgan wrote:
>[color=green]
>> 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.
>>[/color]
> 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[/color]
I trust you didn't mean create a snapshot of the data via using a
make-table query!!!! Ugly.

Bob

re: How to make all access to linked table read-only

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
news:OyeGe.2068$oY.774@newssvr25.news.prodigy.net:
[color=blue]
> 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.[/color]

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

re: How to make all access to linked table read-only

David W. Fenton wrote:[color=blue]
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> news:OyeGe.2068$oY.774@newssvr25.news.prodigy.net:
>
>[color=green]
>>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.[/color]
>
>
> 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.
>[/color]
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

re: How to make all access to linked table read-only

Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote:[color=blue]
> David W. Fenton wrote:[color=green]
>> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
>> news:OyeGe.2068$oY.774@newssvr25.news.prodigy.net:
>>
>>[color=darkred]
>>> 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.[/color]
>>
>>
>> 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.
>>[/color]
> 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[/color]

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

re: How to make all access to linked table read-only

Br@dley wrote:[color=blue]
> Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote:
>[color=green]
>>David W. Fenton wrote:
>>[color=darkred]
>>>"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
>>>news:OyeGe.2068$oY.774@newssvr25.news.prodigy.n et:
>>>
>>>
>>>
>>>>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.
>>>[/color]
>>
>>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[/color]
>
>
> 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?[/color]
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/...ef78d347511c73

But am still open to anything even easier than that.

Bob

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:NKeGe.53577$4o.5708@fed1read06...[color=blue]
> Wayne Morgan wrote:[color=green]
>> 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.
>>[/color]
> 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[/color]

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:fReGe.53578$4o.45887@fed1read06...[color=blue]
> Bob Alston wrote:
> OK but how do I specify that a query is to be/produce a snapshot instead
> of a dynaset?
>
> Bob[/color]

re: How to make all access to linked table read-only

Wayne Morgan wrote:[color=blue]
> Open the query in design view. Right click the background area where the
> tables go and choose Properties. Set Recordset Type to Snapshot.
>[/color]
Very slick. Thank you!!!

Bob

re: How to make all access to linked table read-only

Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
news:btfGe.53580$4o.386@fed1read06:
[color=blue]
> 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.[/color]

Your aims are mutually contradictory.

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

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

re: How to make all access to linked table read-only

David W. Fenton wrote:[color=blue]
> Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
> news:btfGe.53580$4o.386@fed1read06:
>
>[color=green]
>>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.[/color]
>
>
> Your aims are mutually contradictory.
>
> It can be done with Jet user-level security, but that is crackable.
>[/color]
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

re: How to make all access to linked table read-only

Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
news:LHgGe.53581$4o.23649@fed1read06:
[color=blue]
> 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/...-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.[/color]

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 http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: How to make all access to linked table read-only

Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
news:nihGe.53583$4o.15755@fed1read06:
[color=blue]
> Wayne Morgan wrote:[color=green]
>> Open the query in design view. Right click the background area
>> where the tables go and choose Properties. Set Recordset Type to
>> Snapshot.[/color]
>
> Very slick. Thank you!!![/color]

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.

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

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:NKeGe.53577$4o.5708@fed1read06...[color=blue]
> Wayne Morgan wrote:[color=green]
>> 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.
>>[/color]
> 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[/color]

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:NKeGe.53577$4o.5708@fed1read06...[color=blue]
> Wayne Morgan wrote:[color=green]
>> 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.
>>[/color]
> 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[/color]

re: How to make all access to linked table read-only

Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
news:_KyGe.53639$4o.20510@fed1read06:
[color=blue]
> David W. Fenton wrote:[color=green]
>> Bob Alston <tulsaalstonsNOSPAM@cox.net> wrote in
>> news:btfGe.53580$4o.386@fed1read06:
>>[color=darkred]
>>>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.[/color]
>>
>> Your aims are mutually contradictory.
>>
>> It can be done with Jet user-level security, but that is
>> crackable.[/color]
>
> 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.[/color]

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.

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

re: How to make all access to linked table read-only

Wayne Morgan <comprev_gothroughthenewsgroup@hotmail.com> wrote:[color=blue]
> 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.[/color]

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).
[color=blue]
> "Bob Alston" <tulsaalstonsNOSPAM@cox.net> wrote in message
> news:NKeGe.53577$4o.5708@fed1read06...[color=green]
>> Wayne Morgan wrote:[color=darkred]
>>> 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.
>>>[/color]
>> 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[/color][/color]

--
regards,

Bradley

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

re: How to make all access to linked table read-only

Bob Alston 写道：
[color=blue]
> 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[/color]

re: How to make all access to linked table read-only

You are correct, but read this first.

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

--
Wayne Morgan
MS Access MVP


"Br@dley" <n0mail@4u.com> wrote in message
news:s3dHe.68125$oJ.10505@news-server.bigpond.net.au...[color=blue]
> Wayne Morgan <comprev_gothroughthenewsgroup@hotmail.com> wrote:
>
> 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).[/color]

re: How to make all access to linked table read-only

Wayne Morgan wrote in message
<krmHe.3009$1g5.2048@newssvr33.news.prodigy.com> :[color=blue]
> You are correct, but read this first.
>
> http://www.mvps.org/access/bugs/bugs0036.htm[/color]

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

re: How to make all access to linked table read-only

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_vidarNOSPAM@yahoo.no> wrote in message
news:mn.0b5a7d584d5932bd.33955@yahoo.no...[color=blue]
> Wayne Morgan wrote in message
> <krmHe.3009$1g5.2048@newssvr33.news.prodigy.com> :[color=green]
>> You are correct, but read this first.
>>
>> http://www.mvps.org/access/bugs/bugs0036.htm[/color]
>
> 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
>[/color]

re: How to make all access to linked table read-only

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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:NKeGe.53577$4o.5708@fed1read06...[color=blue]
> Wayne Morgan wrote:[color=green]
>> 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.
>>[/color]
> 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[/color]