07 April 2009

Microsoft Access dummy row

If you want to generate a dummy or extra row, say for a combo box control, then you can write a SQL statement like this: select col1 from table union all select 'All' from table. What this statement does is create an extra row with text All in the last row in your combo box.

While testing, we found that this statement works in Microsoft Access 2003 but not in Access 2000. In Access 2000, it works if your table has at least one row, but if your table has no rows, no result is returned! However, if you use the OLEDB interface, the dummy row is returned, so at least we can continue working.