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

Question

I am looking for a way in Microsoft 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.

Answer 1

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.