Question on DB Bloat and persistent connection to linked DB

[XV1957]

Hi Access experts,
I read a lot about Access database bloat when using temporary tables that reside in the front end, which was where I created and used temporary tables before reading about bloat.
I therefore wrote a routine on startup that creates a temporary database and then copies the structure of the temporary tables into that temporary database using the DoCmd.TransferDatabase command. It kills the DB on exiting the application.

I also read about keeping a persistent connection (handle) open to a linked DB as long as the main application is active, and this for efficiency reasons. I got this to work as well.

Being a self-taught Access programmer I have two questions:
1. Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database?
2. If I want to work with the tables in my temporary database I use the following code for example:
Dim dbTemp As Database ‘temporary database
Set dbTemp = DBEngine.Workspaces(0).OpenDatabase(strDBTemp)
Set rstRpt = dbTemp.OpenRecordset("tblRptPU001", dbOpenDynaset) ..
My question is this: if, at the end of the procedure, I write
dbTemp.close
set dbTemp = Nothing
does this leave the persistent connection open? The reason for the question is that I intend to split the database and soon go multi user, which is when efficiency is even more important than when working on a temporary database.
Thanks in advance for your help.

 

[CJ_London]

Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database?

bloat is caused by adding objects (tables/forms/queries/reports/data), so if you are not adding objects the db will not bloat.

My question is this: if, at the end of the procedure, I write
dbTemp.close
set dbTemp = Nothing
does this leave the persistent connection open?

No - the connection will close - do this when the user closes their front end. Don't forget to delete the temp db on close or before trying to create it again

I intend to split the database and soon go multi user

When you do, ensure everyone has their own temporary db (otherwise two users may be trying to create or change it's structure at the same time) - for speed, better that it is located on their local drive rather than the network, although you may have some security issues to consider depending on your company policy and what is held in the temp db.

 

[jdraw]

Here is an often referenced link regarding multiuser access related performance tips.
The most common set up with multiuser is

Frontend ----BackEnd where a copy of the frontend is installed on each user's PC and the Backend on a shared server.

Some have evolved a new term "side end" for temp tables. See more here

Good luck.

 

[XV1957]

Thank you CJ_London,
this very clearly answers my questions. I can go on from here.
I do delete the DB on leaving, and I use the User's name in creating a temp DB, so it is unique to each user.

 

[XV1957]

Hi jdraw,
Thanks for sending these links. I had found them, but do appreciate your help very much.