Solved Temporary Table in Shared Front End (1 Viewer)

jack555

Member
Local time
Today, 22:12
Joined
Apr 20, 2020
Messages
93
I use a temporary table to store values from the form and when the form closes, transfer temp data to another table and clear the temp table. This works fine if one user working on it. If multiple users working on the same front end, then throws a tricky scenario.

How to solve this scenario where front end shared by multiple users and temp table required for some purpose. I am not an expert but a power user.
 

isladogs

MVP / VIP
Local time
Today, 18:12
Joined
Jan 14, 2017
Messages
18,186
There is no problem with using a temp table in the front end
However, each user MUST have their own copy of the front end database on the own workstation

NEVER allow multiple users to share the same front end. Doing that WILL definitely result in corruption.
Its not a matter of IF but WHEN that will happen.
 

plog

Banishment Pending
Local time
Today, 13:12
Joined
May 11, 2011
Messages
11,611
I use a temporary table to store values from the form and when the form closes, transfer temp data to another table and clear the temp table...and temp table required for some purpose

A what and a few whys:

What purpose exactly?
Why isn't the form bound to the ultimate destination table?
Why not have an unbound form and do an INSERT into the ultimate destination upon the form close?
 

jack555

Member
Local time
Today, 22:12
Joined
Apr 20, 2020
Messages
93
A what and a few whys:

What purpose exactly?
Why isn't the form bound to the ultimate destination table?
Why not have an unbound form and do an INSERT into the ultimate destination upon the form close?
  1. It's a survey table. user will answer certain questions and at the end of the survey, the value stored in the temp table should be transferred to the original.
  2. I made this to avoid inserting an incomplete survey in the original table.
  3. the reason it is not unbound is, there is an option group that cannot be unbound in continuous form.

Is there any better way to do this? Please guide me with simple instructions. Thank you.
 
Last edited:

jack555

Member
Local time
Today, 22:12
Joined
Apr 20, 2020
Messages
93
There is no problem with using a temp table in the front end
However, each user MUST have their own copy of the front end database on the own workstation

NEVER allow multiple users to share the same front end. Doing that WILL definitely result in corruption.
Its not a matter of IF but WHEN that will happen.
I agree with this. However, there is a challenge to implement individual copies for each user. Users float in every department and they always not copying the front end to their location. I will try to implement your advice, but need a workaround as well.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:12
Joined
Feb 19, 2013
Messages
16,553
a challenge to implement individual copies for each user
It's not a workaround, its a risky strategy. Allowing users to share a front end will lead to corruption at some point. If they float around the department using different machines, just ensure the FE is loaded on every machine they use, don't rely on the user doing it for you. The front end is not specific to a user as such, more the machine that users are using.

It's easy enough to have the FE check on it's location and if it is on the server it just closes, perhaps with a message 'to run this application it needs to be on your machine'
 

isladogs

MVP / VIP
Local time
Today, 18:12
Joined
Jan 14, 2017
Messages
18,186
I agree with this. However, there is a challenge to implement individual copies for each user. Users float in every department and they always not copying the front end to their location. I will try to implement your advice, but need a workaround as well.
A much bigger challenge will be the time & effort needed to restore the application and/or data after corruption occurs
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:12
Joined
Feb 28, 2001
Messages
26,999
However, there is a challenge to implement individual copies for each user.

This represents a potential design flaw in that you have designed yourself a lot of extra work. Are you suggesting that there are query, form, report, or code variations individualized for each user? Or is it merely that you have some user-specific data in that private table?

Using a copy of a front-end file freshly copied for each run is the ideal case, since that removes any local bloat created by the previous session. But in order for us to give you best advice, we need to know (at least in broad-brush terms) what is unique for each user.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:12
Joined
Feb 19, 2002
Messages
42,971
Just in case three people telling you that sharing a FE is a bad idea, let me add a fourth.

A better way to handle incomplete surveys is to have a "completed" date or flag depending on what constitutes "complete". Queries that select queries, except for the maintenance form, should only select queries where the CompletedDT is not null or the CompletedFlg is True.

If the people bounce around using different PCs (very strange in the current environment), then having any temp tables in the FE is a problem.
 

jack555

Member
Local time
Today, 22:12
Joined
Apr 20, 2020
Messages
93
A what and a few whys:

What purpose exactly?
Why isn't the form bound to the ultimate destination table?
Why not have an unbound form and do an INSERT into the ultimate destination upon the form close?
I did as per your second advice. bound the form to the destination table. The records having the status "draft" until clicking the "save" button. So excluding the records with the draft flags for other purposes. Now successfully achieved the intended result without any issues.

thank you. sometimes small lead makes a big change.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:12
Joined
Sep 12, 2006
Messages
15,614
Here's the solution.
If you have shared front ends, you just can't use local tables for temporary data. I must say I find local temporary tables to be very convenient though.

In addition, do users have separate copies of Office/Access, as you can get problems with different versions of Access automatically changing the Access front end to match their version and preventing users with lower versions continuing to be able to use the database.

Clearly Access is designed to be multi-user, but we tend to find it is just more reliable not to use it as multi-user.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:12
Joined
Feb 19, 2002
Messages
42,971
Clearly Access is designed to be multi-user, but we tend to find it is just more reliable not to use it as multi-user.
From day one, Access had the ability to link to other databases whether they be Jet/ACE or ODBC. It is simply that good practices took a long time to percolate through the community. Samples are always made of monolithic databases simply because it is easier. Also, the people at MS who make the samples don't actually develop using Access so they view Access differently than we do. They look at Access as a "document" similar to the way we look at a Spreadsheet or Word doc rather than as a development platform. But anyone who developed applications for other people learned early on the problem with trying to do maintenance on the FE when people were working in it and so naturally gravitated to splitting. Also, versions of Access prior to A2007 actually allowed multiple people to have a database open and edit objects. This made it easier for two developers to work on the same database. Current versions no longer support that and I'm not sure if there is an add-in anymore that works as a Source Control manager.
 

Users who are viewing this thread

Top Bottom