The TempTables.MDB illustrates how to use a temporary MDB in your app.

The above logic has a relatively minor problem. Each time you link a table it consumes approximately 5 kb in your FE. Thus leading to bloating of the FE. You can safely leave the table(s) linked in your FE even if the temp MDB does not exist. So long, of course, as you don't use the table(s). You may need to change the link though if the FE is placed in a different drive and path on the users computer than your computer.

There is an interesting situation in the above steps where you get Error 70 "Permission denied" when you go to delete the temporary MDB. The problem is that you are using a form to display some data based on one of the linked tables. Even though you've run docmd.close on the form and you've deleted the link to the table the bound form which depends on that table is still open because it is running the code to unlink the tables and delete the temporary MDB. So you need to change the RecordSource of the form to "" before executing the code to unlink the tables and delete the temporary MDB.

(Oh yeah, this took me quite a number of hours to figure out. <sigh>)

A suggestion by a fellow denizen of the comp.databases.ms-access newsgroup was to use the Windows temp directory and a temporary unique file name. Then if there was a problem of some sort when exiting then no big deal. The user can clean up that directory whenever they want to.

Note that the file extension need not be .MDB. So you could create a file named something like 104a98c4.tmp and link to it just fine.

My concern with this approach would be if there is a continuing problem with a fairly large MDB you could run out of disk space at some point and be in deeper trouble.

Another alternative is to wrap every thing in a transaction:

BeginTrans
define table
run queries
run report
Rollback.

However I haven't tried this personally and have no idea how effective this approach is to avoiding bloating. I would suspect this process uses jet temp files in the temp directory.

Another variation by a fellow denizen of the comp.database.ms-access newsgroup is to retain a master copy of this temp tables MDB. He then copies the master copy to a different name and uses the copy.

Also note that you may want to select the database version in the following line of code:

Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral,dbVersion40)

if your database is still in Access 2003 or older format and you are running the database in Access 2007. Thanks to Rick A.B for mentioning this problem in the comp.databases.ms-access newsgruop.

Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony

Contact

Tony's Blog

Table of Contents

The TempTables.MDB illustrates how to use a temporary MDB in your Microsoft Access app.