Create Temporary Table Vs temp table on front end 2

[danwand]

Hi,

Was wondering what the best practise would be for the following senario:

I have a multi-user (well 4 users!) back end/front end Access 2000 db. All PC's have a separate front end.
I have a number of dynamically created crosstab reports that take information from tables that are cleared and populated each time a report is run.
At the moment I have placed these temporary tables in the front end in order to avoid conflicts if 2 users are using the same reports but does this defy the convention of a fe/be database?.

Is this logic correct?

Should the tables be in the back-end?

Or.....

Should i use DAO Create methods instead to create, populate and delete the tables required to hold the temporary information. As i understand it this would resolve and conflicts between users, but will it lead to database bloat?

 

[hneal98]

If you are going to use temp tables, your current logic works. One thing to remember with themp tables is that you need to compact your database on occasions otherwise it iwll keep growing until it slows everthing down.

 

[psemianonymous]

It's all in the engineering-type questions. If you're making small temp tables, I'd recommend you do the simplest thing possible that works. For simple/quick-and-dirty temp tables, I have a single "temp table" on the backend that I have my users write to. Each user gets their own records identified on the table by their Windows username, so they can run a report based off of the data where field "Username" = their current username.

It's ugly, but again, if it's a small enough data set, it doesn't really matter.

The frontend temptable idea is of course a better idea if you're getting large tables or if for whatever reason my scheme above won't work.

Also note that you can put the temptables in an entirely separate database ... I've never done this, but some smarter people than I have. Check out comp.databases.ms-access for more details on how that works.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[danwand]

Thanks hneal and pseale your comments are much appreciated.
I have read around these issues and i am aware of the need to compact and have also seen posts relating to pseale's method and that of creating a separate database for temp tables.
None of the info i have read has been definitive in outlining the best method to use. I just wanted to be sure that leaving the temp tables on the front end isn't going to cause me too many problems.

Again thanks for the input.

Dan

 

[danwand]

I would welcome any other further comments on best practise regarding temp tables.

Dan.

 

[hneal98]

As long as you are aware of the need to compact the database on a regular basis, your current method should be fine. I don't recall how, but a while ago, I wrote code to compact the database after a certain amount of time. I am sure you can find it on the internet or maybe someone else here has an idea for that. Then you won't have to worry about it.

 

[PHV]

As the Front End is single user why not compact/repair on close ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JoeAtWork]

One strategy we often employ is to have the user's shortcut point to a batch file that copies the front-end database to their local machine (from a server directory), then opens it in Access. We do this primarily to make distribution of new front-end version automatic, but for your case it is also good because the user always gets a pristine version of the front-end - in fact it eliminates the need to compact it.