Solved Temporary Table in Shared Front End

[jack555]

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]

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]

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]

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.

 

[jack555]

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]

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]

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]

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.

 

[jack555]

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]

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.