Creating temporary table in multi-user environment

I have the situation where I need to create a temporary table (I think) that holds records with calculated amounts. I am using data from 2 tables with existing data to create records with similar information. The first table has similar information listed up to 5 times on one row. The second table has similar information listed in other rows. This sounds strange, I know, but that is the way the data feed comes in - and the information is really all different, but I need to break it up, rename it, and create individual rows. So the first table can have up to 5 rows created from one row, and then there may be another row from the second table. The first table can have many original rows that are broken up. I have accomplished this with a series of queries - first a maketable query that makes the table and takes the first set of info from the first table. Then a series of append queries that breaks up the rest of the row,appends it, and then finally, an append query that appends info from the second table, if it meets the criteria. This process brings in existing information. Then I have new data that is input directly into that table through a form.

This method works well - that is, my data is correct, and I have built my forms around this new table, and the reports all work well. But here is the dilemma - sometimes the "old" data is changed by the end-user. This new table is only holding a limited number of fields, mostly calculated. If one change is made to the original table, all of the data really needs to be recalculated in the new table. I know I really do not want to and should not be storing these values in a table.

So here's the question - what is a good way to create some sort of temporary storage place for the information run from this series of queries for just the [ClaimNumber] the user is working on? I have a multi-user environment, with the data stored in a separate database on the server. I guess I am worried that if I use a temporary make-table for one user, as soon as another user goes to run the data for their ClaimNumber, the table will be deleted. Is that true? How else can I do this?

Who is Participating?
Yes. Without a split DB, option #1 will work best. There are several ramifications, though, that do not exist in a split DB; there is more than meets the eye. At a minimum, you will need to do these things to work within a single front end:

1. Identify each user at the point the user enters the database, either via Access security or (as I have become accustomed to doing), creating a User table containing a user name & password for each user.
2. Assign the user ID to a global variable (or a control on a hidden form) at login to the application.
3. Have a "logged in" flag in the User table and prevent a user from being logged in if already logged in. That is, do not allow a user to log in if that user's LoggedOn flag is True, update it to True when logged on, and update to False when logged off. This prevents one user ID from being used at multiple stations simultaneously and the data of two instances of the same user being combined/mixed in the temporary table. Of course, you then need to provide for a way to unlock that flag should a user's station crash without resetting the LoggedOn flag to False. (Alternatively, the station (PC) name can be used, but even that would not preclude a user from running two instances of the application from one PC and combining/mixing data from two separate instances of the same process).
4. If using a public variable for the UserID, create a public function to retrieve the UserID, since you cannot reference the public variable directly in your queries.
5. In each query (DELETE, UPDATE, SELECT, or INSERT) involving the temporary data, add a criteria (WHERE clause) for the current user ID, either by calling the public function above or referring to the value of the control on the hidden form.

All of that...or begin distributing the front end to all users. Then you just need a little version control to ensure that nobody can open the backend from a non-updated front end. Tony Toews has a good one that, in the end, may be preferable to all the work above. Here is the link:
Two suggestions:

1. In a non-split database (program and data in one MDB): add a field in the temporary table for the user ID. When clearing, populating, or using the temporary data, filter it all to the current user ID so that many users can use the one table while having their own temporary data secluded from that of others.
2. In a split database (program in one MDB, data in another): deploy a copy of the front end (program to each station and have the temporary table in the front-end, not in the back-end. This way, each station has its own unique copy of the temp table.

Either way, I would stay away from MakeTable queries and just make the tables ahead of time, then issue DELETE * From... (and, in case #1, WHERE...) statements before re-populating.
4charityAuthor Commented:
Thanks for the direction. I do have a split database - but all of the users are accessing the same front end from a server. This is because we are still in development, and making frequent updates to the front end, and there are many users. I am hoping that your suggestion #2 will still work in this situation. I can put the temporary table in the front end, but just thinking it through, I think I will have to use your suggestion #1.

When i get to work tomorrow morning, I will try this out, and am sure I will have some follow up questions on the syntax of working with one user at a time in the table.


4charityAuthor Commented:
This is great information on how to handle the situation. As I get going on it, I will probably post a related question. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.