Read only tables, but still create queries etc.

[Duncs]

I have a BE db, which contains several tables. What I'd like to do is
create another FE database that will be used for MI. The main problem
is that the MI queries cannot be pre-defined entirely just now, and
there will be occasions where they will be written on an ad-hoc basis--
I know, it's possibly not the right way of doing it, but that is
unfortunately how we work!

So, what I need is to have the BE tables linked into the new database,
with the tables being read-only. The MI staff will then be able to
create their queries, reports, forms etc. without being able to delete
data / records or modify any of the entries.

I already have a password on the BE database, so that you cannot load
it unless you know the password. But, I don't want to remove this, or
provide it to the MI team...as I've been bitten by this before, with
people passing the password on to others and everybody and their aunt
accessing / modifying the data. As it's financial data, I don't want
this to happen.

Many TIA

Duncs

 

[Brendan Reynolds]

So, what I need is to have the BE tables linked into the new database,
with the tables being read-only. The MI staff will then be able to
create their queries, reports, forms etc. without being able to delete
data / records or modify any of the entries.

<snip>

You could use JET user-level security to give the users read-only permission
on the tables, or you could store the data in SQL Server - the free Express
edition would probably meet your needs.

JET user-level security doesn't provide a very high level of protection, and
if you're using Access 2007 you'll have to forgo the new ACCDB format and
stick with the older MDB format if you use JET. If you use SQL Server you
can add your users to the db_datareader database role but not to the
db_datawriter database role and there you go - you've got read-only tables.

 

[John Spencer]

If for some reason you can't use the solutions posted by Brendan, you could
create base queries of each table and set them to snapshot. You would not
have ANY tables (except the system tables) in the database.

Tested in Access 2003

How:
== Open a new database
== Open a query in design view
== Set Source Database Property to the path to the backend including the name
of the backend database
== Set the Recordset Type property to Snapshot
== Select Query: Show Table from the menu
== Select a table from the list
== Add all the fields to query (use the * option so you won't need to update
the queries if your tables change)
== Save the query with the table name

I've not tested this for performance and use of indexes, but I think it will
behave close to the same as an Access database. You could add another layer
of confusion, by making these queries HIDDEN and then using the hidden queries
as the source for a visible query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duncs]

Both suggestions sound good, although I may need to ask some more
questions about your suggestion John.

I've not had a chance to try anything yet, but I'll have a go and post
back my results.

Cheers

Duncs

 

[Duncs]

John,

Tried your suggestion and it's great. Does exactly what it says on
the tin!!

Brendan, I started down the road of looking at your suggestion but, I
have to admit, I got lost. Due to time constraints, I had to abandon
it and look at John's suggestion, which works. However, many thanks
for your suggestion. I'll have a look at it when I get the chance and
see how easy it is.

Many thanks to both of you.

Duncs

 

[Duncs]

John,

I have a problem...

I've created the queries and named them as the tables, just as you
suggested. On each ocassion, I was asked to input the BE password,
which was accepted OK. When I try to access one of the table queries,
I get a message advising "Not a valid password". This also means that
I cannot create any queries based on this table, as when I try to add
it into the query window, I get the same error.

Any ideas?

Duncs

 

[John Spencer]

I did not test for a password protected database.

I don't know if it is possible (I am speculating), but it should be possible
to include the password in the specification. Hopefully someone can provide
the answer on how to do this.

I will try to do some research later or you can Bing or Google the Access
groups and see if you can find a solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Ok, try this

Clear the Source Database property and
set the Source Connect Str of the source queries to something like the following:

MS Access;PWD=spencer;DATABASE=C:\Center Databases\Copy of Newsgroup
Answers_2K.mdb

Or change the query SQL to something like
SELECT *
FROM CalendarTable IN '' [MS Access;PWD=spencer;DATABASE=C:\Center
Databases\Copy of Newsgroup Answers_2K.mdb];


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duncs]

John,

That seems to work fine, with no problems...for now at least!

Thanks

Duncs

Ok, try this

Clear the Source Database property and
set the Source Connect Str of the source queries to something like the following:

MS Access;PWD=spencer;DATABASE=C:\Center Databases\Copy of Newsgroup
Answers_2K.mdb

Or change the query SQL to something like
SELECT *
FROM CalendarTable IN '' [MS Access;PWD=spencer;DATABASE=C:\Center
Databases\Copy of Newsgroup Answers_2K.mdb];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I did not test for a password protected database.

I don't know if it is possible (I am speculating), but it should be
possible to include the password in the specification.  Hopefully
someone can provide the answer on how to do this.

I will try to do some research later or you can Bing or Google the
Access groups and see if you can find a solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

- Show quoted text -

 

[Duncs]

John, one more thing.

If the 'table' query has its 'Record Set Type' property set to
"Snapshot", when does the information get updated? Is it done
dynamically?

Duncs

John,

That seems to work fine, with no problems...for now at least!

Thanks

Duncs

Ok, try this

Clear the Source Database property and
set the Source Connect Str of the source queries to something like the following:

MS Access;PWD=spencer;DATABASE=C:\Center Databases\Copy of Newsgroup
Answers_2K.mdb

Or change the query SQL to something like
SELECT *
FROM CalendarTable IN '' [MS Access;PWD=spencer;DATABASE=C:\Center
Databases\Copy of Newsgroup Answers_2K.mdb];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Queries are windows on the data. When the table is updated and the query
executes you see the latest data in the table.

If you have the query open and are viewing the data, you will see the data as
it was when the query executed.

So basically the information is updated dynamically.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, one more thing.

If the 'table' query has its 'Record Set Type' property set to
"Snapshot", when does the information get updated? Is it done
dynamically?

Duncs

John,

That seems to work fine, with no problems...for now at least!

Thanks

Duncs

Ok, try this
Clear the Source Database property and
set the Source Connect Str of the source queries to something like the following:
MS Access;PWD=spencer;DATABASE=C:\Center Databases\Copy of Newsgroup
Answers_2K.mdb
Or change the query SQL to something like
SELECT *
FROM CalendarTable IN '' [MS Access;PWD=spencer;DATABASE=C:\Center
Databases\Copy of Newsgroup Answers_2K.mdb];
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John Spencer wrote:
I did not test for a password protected database.
I don't know if it is possible (I am speculating), but it should be
possible to include the password in the specification. Hopefully
someone can provide the answer on how to do this.
I will try to do some research later or you can Bing or Google the
Access groups and see if you can find a solution.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Duncs wrote:
John,
I have a problem...
I've created the queries and named them as the tables, just as you
suggested. On each ocassion, I was asked to input the BE password,
which was accepted OK. When I try to access one of the table queries,
I get a message advising "Not a valid password". This also means that
I cannot create any queries based on this table, as when I try to add
it into the query window, I get the same error.
Any ideas?
Duncs
John,
Tried your suggestion and it's great. Does exactly what it says on
the tin!!
Brendan, I started down the road of looking at your suggestion but, I
have to admit, I got lost. Due to time constraints, I had to abandon
it and look at John's suggestion, which works. However, many thanks
for your suggestion. I'll have a look at it when I get the chance and
see how easy it is.
Many thanks to both of you.
Duncs
Both suggestions sound good, although I may need to ask some more
questions about your suggestion John.
I've not had a chance to try anything yet, but I'll have a go and post
back my results.
Cheers
Duncs
If for some reason you can't use the solutions posted by Brendan,
you could
create base queries of each table and set them to snapshot. You
would not
have ANY tables (except the system tables) in the database.
Tested in Access 2003
How:
== Open a new database
== Open a query in design view
== Set Source Database Property to the path to the backend
including the name
of the backend database
== Set the Recordset Type property to Snapshot
== Select Query: Show Table from the menu
== Select a table from the list
== Add all the fields to query (use the * option so you won't need
to update
the queries if your tables change)
== Save the query with the table name
I've not tested this for performance and use of indexes, but I
think it will
behave close to the same as an Access database. You could add
another layer
of confusion, by making these queries HIDDEN and then using the
hidden queries
as the source for a visible query.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Brendan Reynolds wrote:
<snip>
So, what I need is to have the BE tables linked into the new
database,
with the tables being read-only. The MI staff will then be able to
create their queries, reports, forms etc. without being able to
delete
data / records or modify any of the entries.
<snip>
You could use JET user-level security to give the users read-only
permission on the tables, or you could store the data in SQL Server -
the free Express edition would probably meet your needs.
JET user-level security doesn't provide a very high level of
protection,
and if you're using Access 2007 you'll have to forgo the new ACCDB
format and stick with the older MDB format if you use JET. If you use
SQL Server you can add your users to the db_datareader database
role but
not to the db_datawriter database role and there you go - you've got
read-only tables.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Duncs]

Cheers John. Your suggestion and support has been a great help.

Many thanks.

Duncs

Queries are windows on the data.  When the table is updated and the query
executes you see the latest data in the table.

If you have the query open and are viewing the data, you will see the data as
it was when the query executed.

So basically the information is updated dynamically.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, one more thing.

If the 'table' query has its 'Record Set Type' property set to
"Snapshot", when does the information get updated?  Is it done
dynamically?

Duncs

John,
That seems to work fine, with no problems...for now at least!
Thanks
Duncs
Ok, try this
Clear the Source Database property and
set the Source Connect Str of the source queries to something like the following:
MS Access;PWD=spencer;DATABASE=C:\Center Databases\Copy of Newsgroup
Answers_2K.mdb
Or change the query SQL to something like
SELECT *
FROM CalendarTable IN '' [MS Access;PWD=spencer;DATABASE=C:\Center
Databases\Copy of Newsgroup Answers_2K.mdb];
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John Spencer wrote:
I did not test for a password protected database.
I don't know if it is possible (I am speculating), but it should be
possible to include the password in the specification.  Hopefully
someone can provide the answer on how to do this.
I will try to do some research later or you can Bing or Google the
Access groups and see if you can find a solution.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Duncs wrote:
John,
I have a problem...
I've created the queries and named them as the tables, just as you
suggested.  On each ocassion, I was asked to input the BE password,
which was accepted OK.  When I try to access one of the table queries,
I get a message advising "Not a valid password".  This also meansthat
I cannot create any queries based on this table, as when I try to add
it into the query window, I get the same error.
Any ideas?
Duncs
John,
Tried your suggestion and it's great.  Does exactly what it sayson
the tin!!
Brendan, I started down the road of looking at your suggestion but, I
have to admit, I got lost.  Due to time constraints, I had to abandon
it and look at John's suggestion, which works.  However, many thanks
for your suggestion.  I'll have a look at it when I get the chance and
see how easy it is.
Many thanks to both of you.
Duncs
Both suggestions sound good, although I may need to ask some more
questions about your suggestion John.
I've not had a chance to try anything yet, but I'll have a go andpost
back my results.
Cheers
Duncs
If for some reason you can't use the solutions posted by Brendan,
you could
create base queries of each table and set them to snapshot.  You
would not
have ANY tables (except the system tables) in the database.
Tested in Access 2003
How:
== Open a new database
== Open a query in design view
== Set Source Database Property to the path to the backend
including the name
of the backend database
== Set the Recordset Type property to Snapshot
== Select Query: Show Table from the menu
== Select a table from the list
== Add all the fields to query (use the * option so you won't need
to update
the queries if your tables change)
== Save the query with the table name
I've not tested this for performance and use of indexes, but I
think it will
behave close to the same as an Access database.  You could add
another layer
of confusion, by making these queries HIDDEN and then using the
hidden queries
as the source for a visible query.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Brendan Reynolds wrote:
<snip>
So, what I need is to have the BE tables linked into the new
database,
with the tables being read-only.  The MI staff will then be able to
create their queries, reports, forms etc. without being able to
delete
data / records or modify any of the entries.
<snip>
You could use JET user-level security to give the users read-only
permission on the tables, or you could store the data in SQL Server -
the free Express edition would probably meet your needs.
JET user-level security doesn't provide a very high level of
protection,
and if you're using Access 2007 you'll have to forgo the new ACCDB
format and stick with the older MDB format if you use JET. If you use
SQL Server you can add your users to the db_datareader database
role but
not to the db_datawriter database role and there you go - you've got
read-only tables.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -