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" <tu****************@cox.net> wrote in message
news:W%dGe.53575$4o.43523@fed1read06...

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

[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]

Bob Alston wrote:

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

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

Bob

[Bob Alston]

Bob Alston wrote:

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

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" <co***************************@hotmail.com> wrote in
news: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]

David W. Fenton wrote:

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news: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.

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]

Bob Alston <tu****************@cox.net> wrote:

David W. Fenton wrote:

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news: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.

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

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]

Br@dley wrote:

Bob Alston <tu****************@cox.net> wrote:

David W. Fenton wrote:

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news:Oy***************@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.

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

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?

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

[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" <tu****************@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

[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" <tu****************@cox.net> wrote in message
news:fReGe.53578$4o.45887@fed1read06...

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

Bob

[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 <tu****************@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.

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

[Bob Alston]

David W. Fenton wrote:

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

[David W. Fenton]

Bob Alston <tu****************@cox.net> wrote in
news:nihGe.53583$4o.15755@fed1read06:

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!!!

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

[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" <tu****************@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

[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" <tu****************@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

[David W. Fenton]

Bob Alston <tu****************@cox.net> wrote in
news:_KyGe.53639$4o.20510@fed1read06:

David W. Fenton wrote:

Bob Alston <tu****************@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.

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

[Br]

Wayne Morgan <co***************************@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" <tu****************@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

--
regards,

Bradley

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

[yulanyou]

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]

You are correct, but read this first.

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

--
Wayne Morgan
MS Access MVP
"Br@dley" <n0****@4u.com> wrote in message
news:s3******************@news-server.bigpond.net.au...

Wayne Morgan <co***************************@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).

[RoyVidar]

Wayne Morgan wrote in message
<kr*****************@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" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...

Wayne Morgan wrote in message
<kr*****************@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

[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" <tu****************@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