Creating a Temporary Table/RecordSet in Multi-User Database Chuck W 08.06.2006 18:25:02 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.

Re: Creating a Temporary Table/RecordSet in Multi-User Database "Joan Wild" <jwild[ at ]nospamtyenet.com> 08.06.2006 18:42:55 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: [Quoted Text] > 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.

Re: Creating a Temporary Table/RecordSet in Multi-User Database Chuck W 08.06.2006 19:01:02 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" wrote: [Quoted Text] > 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. > > >

Re: Creating a Temporary Table/RecordSet in Multi-User Database "Joan Wild" <jwild[ at ]nospamtyenet.com> 08.06.2006 19:35:07 Chuck W wrote: [Quoted Text] > 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. -- Joan Wild Microsoft Access MVP

Re: Creating a Temporary Table/RecordSet in Multi-User Database Chuck W 08.06.2006 20:00:02 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 "Joan Wild" wrote: [Quoted Text] > Chuck W wrote: > > 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. > > -- > Joan Wild > Microsoft Access MVP > > >