Read only tables, but still create queries etc.
Duncs
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
"Duncs" <true.kil...@gmail.com> wrote in message
news:8657e5a9-d737-4042...@d10g2000yqh.googlegroups.com...
<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.
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.
--
Brendan Reynolds
John Spencer
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
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" <true.kilted.s...@gmail.com> wrote in message
> >news:8657e5a9-d737-4042...@d10g2000yqh.googlegroups.com...
> > <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 -
Duncs
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
> > - Show quoted text -- Hide quoted text -
Duncs
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 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
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
That seems to work fine, with no problems...for now at least!
Thanks
Duncs
Duncs
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 Spencer
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.
Duncs
Many thanks.
Duncs