Creating a Temporary Table/RecordSet in Multi-User Database

[Guest]

I have a secure database on a network drive with user access control, etc.
There is some VBA code which uses a temporary table (dbOpenTable) which is
populated with a unique set of records which the user then works with that
data, generates reports and such.
The problem is that when another user logs in and does the same thing, it
overwrites the table so the first user is suddenly using the new user's data,
not their data. There are many many queries built off this table so I really
need to find a way to either (a) create a unique copy of this table for each
user while magically usign the same table name so I don't have to change all
my existing queries or (b) make the database exclusive.

It appears that exclusivity is set by each user. If I set the Advanced
Option Default Open Mode to Exclusive, it doesn't consistenly prevent others
from using it. I set it to exclusive on my machine but another user could get
in and their option was set to Shared. I really can't go to each user and set
their option to Exclusive.

 

[Joan Wild]

Any multiuser database should be split. The 'backend' mdb (contains just
the tables/relationships) would sit on the network drive.

A copy of the 'frontend' mdb (containing all other objects) would sit on
each user's PC. The frontend would contained linked tables, linked to the
backend.

In your case, you'd have this temporary table created in the frontend.
Therefore there wouldn't be any clashes between users, as they would have
their own temporary table.

However, is this table really necessary? You should be able to accomplish
what you need using queries. Also keep in mind that when the user creates
their table in the frontend, they won't have any subsequent changes that
might have happened since they created their table.

If you are going to split the mdb, since it's secured don't use the splitter
wizard or the backend won't be secure. Instead split it manually:
http://www.jmwild.com/SplitSecure.htm

 

[Guest]

The problem with splitting the database is distributing the frontend to all
the users and tracking that they have the right users. Don't have enough
admin bandwidth to really do this.

I wondered about using a query but that would seem to me to require changing
all the queries that currently use that (and other tables/queries) to now use
that query in place of the table. And how do I generate that query in VB if
there's no underlying table? To help explain the situation more, I am
building a temporary "organization chart" for a selected manager from a table
of all employees. The user selects a manager and the depth of organization
they want to build. The code looks at each employee to see if they have the
selected manager as their manager. If so, they're added to the table. Then
the code recurses with that most recent employee to see if anyone reports to
them. The table is global and the code adds a new record to the table each
time the code finds an employee within the selected manager's organization. I
don't see how to use the existing VB code to add records to a query instead
of a table. Make sense?

Siince we're an FDA regulated company, everything has to be validated and
that takes a lot of time some I'm looking for a minimal change. There may not
be one.

Any other options?

Thanks,

Chuck

 

[Joan Wild]

The problem with splitting the database is distributing the frontend
to all the users and tracking that they have the right users. Don't
have enough admin bandwidth to really do this.

You can automate updating the users when the frontend changes. See
http://www.granite.ab.ca/access/autofe.htm
for one method (and more links at the bottom for alternatives).

'tracking that they have the right users'- I don't understand this. Users,
Groups, memberships, passwords are all stored in the mdw file, which should
remain with the backend on the server. It's only the permissions that are
stored in the mdb file (and when you copy them an updated frontend, it will
contain all the permissions.

 

[Guest]

Sorry. I should have typed "right versions" not "right users". I'll take a
look at the updater, but we'd have to validate that too so it would take time.

I think the easiest solution is to do the split (something I'm working on
now) then email the front end to people.

Thanks,

Chuck