• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 34
  • Last Modified:

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

Hi,

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.
0
WS
Asked:
WS
2 Solutions
 
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.
0
 
Dale FyeCommented:
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).
3
 
WSStudentAuthor Commented:
@John, if i put the temporary table to each user front end, how i will put that data back to main tables?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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...
0
 
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.
1
 
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.
1
 
PatHartmanCommented:
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.
1
 
WSStudentAuthor Commented:
@John, thanks that work.

@PatHartman, thanks for explaining so well.

@Dale, your articles are quit helpful.

Thanks Fabrice and Gustav.
0

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now