Creating temporary table in multi-user environment

Posted on 2009-12-16
Medium Priority
Last Modified: 2013-11-29
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?

Question by:4charity
  • 2
  • 2

Expert Comment

ID: 26068542
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.

Author Comment

ID: 26068680
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.



Accepted Solution

HartCraft earned 2000 total points
ID: 26068737
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: http://www.autofeupdater.com/

Author Closing Comment

ID: 31667114
This is great information on how to handle the situation. As I get going on it, I will probably post a related question. Thanks.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question