Creating a temporary table similar to Oracle's temporary table behaviour

Question

Hello

I am looing for a way in access to create a temporary table whose behaviour is similar to Oracle's in that its content is gone after the mdb is closed.

As far as I can tell it's not possible, but I am open to be corrected on this.

Should it not be possible, I am still looking for a way to use temporary table but makes sure that the temporary table and/or its content does not clutter the mdb if (for example) the mdb is not closed correctly.

Thanks for any pointer / Rene

Answer 1

A Disconnected Recordset may suit.

Answer 2

Perhaps you could setup your access database to compact and repair on close. To do so (depending on your version, perform the following):

1. Open the Access database or Access project that you want Microsoft Access to compact automatically.
2. On the Tools menu, click Options.
3. Click the General tab.
4. Select the Compact on Close check box.

Access databases are designed to allow themselves to consistently increase in size. When you delete a record from the database, the space allotted for that record continues to be held in reserve by the database and it is not released for reuse. Likewise, extra space, created by modifying and shortening records, is not released for reuse. Compacting and repairing alleviates this.

The compact and repair utility reduces the physical size of your database by making an exact duplicate of the database while also ridding the database of any excess space created by deleting and modifying data. The Compact Utility restructures table records and objects, and then stores them in successive memory blocks, eliminating wasted space. The utility also updates table statistics to reflect database characteristics of the restructured data. The more additions, deletions and modifications performed on databases, the more often they should be compacted

If you never compact your access database, your database will retain the "wasted" space and continue to consume additional space to accommodate additional data. As an example, if you delete all of the records from a 1MB Microsoft Access database and then add 2MB of data, your database will be in excess of 3MB. The 1MB of space, which was allocated to the deleted records, is held by the database as wasted space, which only serves to corrupt your database and fragment your data.

Answer 3

You will not be corrected, Microsoft Access does not directly support temporary tables. All tables created in the database must be explicitly dropped to remove them, and are visible to all users.

The "traditional" way to work around this is to use two Access databases, one with the shared data and a second, for which each user has a private copy, with user interface elements. The second database has the tables from the first one linked and any tables created in the second database are private to the user. In such a case (assuming you don't want to persist user-specific data) you can frequently empty the private database and compact it or even simply copy a new one off the network periodically (you need to do this to update the user interface when changes are made.

Answer 4

Nothing that automatically deletes other than possibly disconnected recordsets.

See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app.

Answer 5

You might look into using transactions. In that way, you could create your table inside a transaction, do what you need with it and then roll it back in the end, i.e., avoid the final commit. That would also allow you to avoid ADO, which is required for disconnected recordsets.

You would not want to create this table in your front or back end, even inside a transaction that is going to be rolled back, because it can lead to fragmentation and bloat (even though not as much as deleting data pages that have been committed). That is, while the data inside a not-yet-committed transaction is stored in a temp file and not in the destination database, there is surely a certain amount of overhead that is good to avoid. Thus, if I were doing the transaction I'd do it on a temp database so as to not in any way impinge on the production front end or back end.

Answer 6

Microsoft Access does not directly support temporary tables

If you look at a SHOWPLAN.OUT query execution plan, it is clear that the engine has the ability to create temporary tables.

If you look at the Access 2007 Help it may be that temporary table functionality could easily be exposed to end users:

CREATE TABLE Statement

CREATE [TEMPORARY] TABLE...

When a TEMPORARY table is created it is visible only within the session in which it was created. It is automatically deleted when the session is terminated. Temporary tables can be accessed by more than one user.

As far as I know, no one has come up with an explanation for this apparent documentation error. I've reported it to the Access Team when it appeared in Access2003 (no reply, naturally), same again for Access2007. Who knows, maybe temporary tables are coming soon...?