MS Access - Temporary Query/Form instead of temporary table in Split Database


I have an Access database, which is split into front end and back end. The front end has all the form,queries,reports and back end has tables. 5,6 users work at the same time on that. there is one form on front end as "frmImport", that import data from excel sheet to subform (which is linked with table called temporary) and then when a button is click all the data goes to specific core/actual tables. Now the problem with this is that as the subform (which is linked with table) that store temporary data, when concurrent users are working like 2,3 user at the same time and they are importing data each user get the data for other user also which is a mess, as the database is split temporary table which is in the back end is same for all the users , is there any way to make temporary query which store the data , as query is in the front end so even if 2,3 user are working at a time that data won't mess with other user data.  Any help how to solve this?

Thank you.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Just import the excel to a temp file on the FE of each user. I guess that right now you have this temp table placed in the BE...but given the fact that is a temp table it belongs to FE not BE.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
You might want to check out my article on Creating temporary tables in Access.   It has a sample database which contains a function which I use to create the temporary table (either in the FE, or in an external table which the function actually links back into the FE).
WSStudentAuthor Commented:
@John, if i put the temporary table to each user front end, how i will put that data back to main tables?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

John TsioumprisSoftware & Systems EngineerCommented:
How you do it now ? ...just think that the import takes place on each user's FE and the data are pushed to the BE...just move the temporary table to the FE ...delete it from the BE and i think you should be good to go...the functionality should remain the same...
Fabrice LambertFabrice LambertCommented:
@John, if i put the temporary table to each user front end, how i will put that data back to main tables?
Your front end should have links to the back end, and links act as tables.

Any query will work, no matter tables are linked or not.
Gustav BrockCIOCommented:
You could also ad a field to the temp table in the backend: UserID

Fill that with the ID of the user and, when reading, filter on that ID.
Temp tables bloat the database so I avoid them when possible.  When I can't avoid them, I make them in the FE as John suggested but ONLY because every time I open the FE, a new, clean copy is brought down from the server and so bloating isn't an issue.  Of course, if this import process might have to be carried over until the next day, I wouldn't be able to use this technique.  Another bloat avoidance technique I use is to create a template database with empty tables.  Then, when I want to start a process that requires temporary data, the code copies the template and renames it.  As long as the name of the temp BE remains constant, the table links are not affected.  The temp database stays constant until the process starts again so if the import takes a few days, there isn't a problem.

The final technique is one I use in SQL Server (which isn't subject to the bloat issue that Jet/ACE is).  This one uses a permanent table but the records include a userID so all the queries that access this table filter data based on who stored it.  That allows the users to share the same table but not interfere with each other.  You could do the same thing with Jet/ACE but the Add/Delete cycle creates the same bloat problem that is created by creating and deleting tables.  Access cannot recover the freed space without a compact.  If you compact the BE regularly, you can get away with temp tables.  If you don't, then you need to use one of my "bloat free" suggestions.
WSStudentAuthor Commented:
@John, thanks that work.

@PatHartman, thanks for explaining so well.

@Dale, your articles are quit helpful.

Thanks Fabrice and Gustav.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.