Question on DB Bloat and persistent connection to linked DB

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.

Question on DB Bloat and persistent connection to linked DB