Want to construct a temporary database

[Guest]

I have some users that want to be able to construct on-the-fly queries for
exporting and reports. I am trying to keep them from working in the database
window of the production database. So here's my thinking, I would provide a
second database that upon opening, imports tables from the production
database using the TransferDatabase Method. The users can create queries,
reports what ever they want and on exit the tables are deleted. Since the
import would happen each time the database is opened, they would have current
data. I was wondering though if there are other users in the production
database doing data entry, if there would be a problem or a risk of
corrupting the db during transfer? Also, how would I delete the tables as the
database closed?
Thanks

 

[John Nurick]

From what you say it sounds as if you have your data in one mdb file
with multiple users opening and using this. Even if the users are not
creating and modifying queries (and goodness knows what else), this
greatly increases the chance of corrupting your data.

Instead, you should split the database. All the data would be in one
"back end" mdb file in a shared folder, and all the standard queries,
forms, reports, etc. would be in a "front end" mdb with linked tables
connected to the back end. Each user would have their own copy of the
front end.

That way, (a) users do not need to make any design changes in the back
end (and ideally you should set up user level security to make sure that
they can't), and (b) they can make any design changes they like in their
own copies of the front end without affecting anyone else.

(Normally in this situation, any time you upgrade the database you'd
just distribute the new front end to the workstations, overwriting the
previous version - and therefore any modifications made by the users. If
you need to preserve user customisations as far as possible while
distributing the new front end, things get a bit more complicated.)

 

[Guest]

Thanks for the input John, but to clarify.
This is a split database. I do have efront-ends linked to a single backend.
I am not using Access security (for a lot of reasons) to restrict what users
can do to the data. So I would rather have a way that some users can create
on the fly queries/reports with a copy of the current data rather than act on
the production data. the challenge is that most of these users have been
used to having access to the data and some of them have been destructive,
i.e. some have been creating queries and deleting records from the dataset
without realizing they were deleting records from the tables. The politics of
the corporate culture has been making it difficult to utilize restrictive
security measures. Everyone in this envirionment has there own needs when it
comes to what they can do with this data.
I would rather give them the freedom to work with a copy of the data in a
separate envirionment as they please without actually touching the live data.
Only three out of twenty or so users would have add/change/delete rights to
the live data.

 

[John Nurick]

This sounds exactly like the sort of situation where user-level security
is needed to protect the data from the users <g> Everyone would have
access to all of it, but only a trusted few would have permissions to
change it, and then only in the ways allowed by the standard front end.
All the "custom" front ends would have read-only access to the linked
tables, though users could create whatever local tables they wanted.

However: the problem with copying a "snapshot" of the production data
into a separate database where a user can do whatever he or she likes
with it is that if the production data is being edited/appended while
the copying is done, it's not possible to be certain that there are no
inconsistencies in the copied data ... for the same reasons you can't
safely back up an mdb file while it's in use. (This applies to an mdb
back end but not, AFAIK, to SQL Server.)

If the production data doesn't change much I suppose you could ignore
the problem and hope for the best, but a better approach might be along
these lines:

1) create a "template" mdb file in a shared folder, with all the tables
(but no data), forms, etc. you want the users to start with in their
"custom" front ends.

2) write a script (VBScript or another OLE-compatible scripting
language) that makes a copy of the "template" in a shared folder and
executes a series of SQL INSERT INTO statements to populate it with data
from the back end mdb.

3) set up a scheduled task on the server that runs the script every
night

4) give the users a shortcut to a batch file that copies the "populated
template" mdb to their local drive and launches it.