Create an MsAccess Oracle like DUAL table

From http://gpgonaccess.blogspot.com/2010/03/making-dual-table.html

Sunday, March 14, 2010
Making A "DUAL" Table
My friend, Banana, likes to push the envelope sometimes. He's a first class developer who's always trying out new ideas. So it was no surprise to me when he asked me if I knew what a "DUAL Table" was. I figured I was in for another trip past the edge of my existing knowledge and I was not wrong.

It's More Than Just a Two-Fer

Turns out that a DUAL table is a feature in Oracle. I won't waste space here on the background stuff. You can read about it here .

Oracle would use DUAL in a SQL statement like this:

SELECT 1+1
FROM DUAL

which returns 2.

In most queries, we don't really have much need for DUAL in Access. We can write SQL statements like this:

SELECT 1+1;

and get valid results.

However, there is at least one place where the equivalent of a DUAL table would be useful--certain UNION queries. Specifically, we often populate "searching" combo boxes with a union query like this:

SELECT CompanyID, CompanyName
FROM tblCompany
UNION SELECT 0 AS CompanyID, " New" as CompanyName
FROM tblCompany;

The AfterUpdate event of such combo boxes either moves the form's focus to the selected record (which would be the Company identified by the selected Primary Key in this case), or if 0 is selected, it moves the form's focus to a new record.

This works well in almost all cases except one. If there are not yet any records in the table (e.g., no company records yet in tblCompany), this Union query returns no records and the combo box is blank; you can't even select " New" to add the first company name.

To get around that, we have traditionally used one of the system tables (e.g. MSysObjects) in the second FROM clause in place of the roster table, which might be empty when the application first goes into use.

SELECT CompanyID, CompanyName
FROM tblCompany
UNION SELECT 0 AS CompanyID, " New" as CompanyName
FROM MSysObjects;

I have never been really comfortable with that, although it is safe enough, I suppose. My main objection isn't technical; it is that this can expose the name of a system table to a user, and perhaps open the door to unwise exploration. I cringe when I think of someone looking at that SQL and wondering, "I wonder what this MSysObjects thingee is?"

Anyway, as we were discussing the concept of DUAL tables and talking about how to create them in Access (not too hard, it turns out), it occurred to us that creating our own Access equivalent of the "DUAL" table might be a good alternative to using an existing system table in a UNION query. There may well be other places where it can be a handy tool.

To be most effective, this table would need to have the same characteristics as the DUAL table:

a) It should be hidden so that users are not tempted to use it for other purposes.
b) It should consist of a single column and contain a single record.
c) It should not be possible to change the default value of that record.

Banana (aka BananaRepublic) provided the DDL statements to do this and he and GroverParkGeorge incorporated them into a form. We wrapped it all in a demo, which you can download at my website. This zip file contains mdb (2003) and accdb (2007) versions.

You can create one or more hidden "Dual" tables in any database to which you have access, and even give them a custom Name. Our demo, for example, already contains hidden "dual" tables named "Banana" and "GPG". You can check them out by opening the two queries by those names.

We hope you find this method of creating hidden DUAL tables interesting and useful.

Here's the link again: Create an Access Dual

Don't Push the Big Red Button

Please be aware that the method we used to hide the DUAL table is not the standard method you can invoke in the Database Container or Navigation Pane. Those tables can be seen if you select the option to show "Hidden" objects. We used the Hidden Objects method which completely hides them. There is a caveat to using this method.

In Jet 3.5, these tables are deleted by a Compact and Repair operation.

This does not happen in Jet 4.0 (and ACE), but you should not deploy any solution that relies on one of these DUAL tables until you have thoroughly tested it.