Re: Creating a Temporary Table/RecordSet in Multi-User Database

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


--
Joan Wild
Microsoft Access MVP

Chuck W wrote:
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.


.

Relevant Pages

  • Re: Comiling problems
    ... (No private e-mails, please) ... > networked PCs each with a 'frontend' mdb that uses linked ... > tables from the 'backend'. ... > tried creating a new mdb and importing the tables from the ...
    (microsoft.public.access.modulesdaovba)
  • Comiling problems
    ... selecting debug> compile all modules. ... I have an A2K 'backend' mdb on one PC, ... networked PCs each with a 'frontend' mdb that uses linked ...
    (microsoft.public.access.modulesdaovba)
  • Re: Multi users Logon Security questions
    ... > The next thing I need to do is split the database so others can work ... and navigate to the mdb - copy the mdb. ... You can use your desktop shortcut (which will open your frontend). ... Ctrl-O and open the backend mdb. ...
    (microsoft.public.access.security)
  • Re: Adding a database to existing workgroup file
    ... Some don't even need the mdw to do so. ... the frontend on each person's PC. ... links to the tables in the backend. ... the database splitter on a secure mdb - the backend will end up unsecured. ...
    (microsoft.public.access.security)
  • Re: Security wizard
    ... I would still give each user a copy of the frontend. ... Microsoft Access MVP ... "Joan Wild" wrote: ... you can't make design changes to a mdb ...
    (microsoft.public.access.security)
Loading