MS-Access: is it possible to display 'virtual' rows in a continuous form?

Question

Hello

I am looking for a method to display virtual rows in an MS Access application, that is, rows that don't actually exist in any table, but only in memory.

I could probably create a temporary table as I need it, fill it with my 'virtual row' values and then display the temporary table's content in the continuous form. Yet, if possible, I'd like to such a thing without using any temporary tables.

Thanks for any help

Rene

edit: When I refer to a 'row in memory', I mean to say that I have a few variables (possibly stored in an array of arrays) that I want to display on the gui/form the same way as rows stored in a table.

Answer 1

You could add entries to a list box with multiple columns. Not exactly a continuous form but close. Ensure the Row Source Type of the list box is Value List.

Me.SomeListBox.RowSource = ""
Me.SomeListBox.AddItem "record1;field2;field3"
Me.SomeListBox.AddItem "record2;field2;field3"

Answer 2

UNION allows you to include virtual rows and virtual fields (columns) can be defined by including:

data As fieldname

FROM can be any table or query in the database, but it must contain at least one row otherwise you'll get an zero-row result set.

So:

SELECT -1 As ID, True As Virtual, "All" As Stuff FROM Table1
UNION
SELECT 0 As ID, True As Virtual, "None" As Stuff FROM Table1

You do not have to include any real rows, but you can:

UNION 
SELECT  ID, False As Virtual, Stuff FROM Table1

This method is often used to add "All" or "None" to listboxes or comboboxes.

Answer 3

ADO allows you to create disconnected recordsets, and an ADO recordset can be assigned as the form's recordset, so that sounds like the way to go.

But I'd caution that I don't use ADO for any functionality that is provided by DAO, and while DAO has nothing like disconnected recordsets (though for some operations you can use a transaction to fake it), I've never needed them myself, so I'm really reporting second-hand information.

However, this is likely a profitable thing for you to investigate, at least insofar as I understand your question.

Answer 4

While you can do this using UNION:

SELECT 0 AS ID, True AS Virtual, 'None' AS Stuff 
  FROM Table1
UNION 
SELECT ID, False, Stuff 
  FROM Table1;
UNION
SELECT -1, True, 'All'
  FROM Table1;

...its probably better to do this using UNION ALL with the DISTINCT keyword:

SELECT DISTINCT 0 AS ID, True AS Virtual, 'None' AS Stuff 
  FROM Table1
UNION ALL
SELECT ID, False, Stuff 
  FROM Table1;
ORDER BY ID
UNION ALL
SELECT DISTINCT -1, True, 'All'
  FROM Table1;

Why prefer UNION ALL? UNION preforms an implicit sort on the data, which you may not want; if you do use an explicit sort then use one! (As the documentation will tell you, an implicit sort is not guaranteed so don't rely on it.)

In order to performs the sort, only the first 255 characters of MEMO data will be considered and in extremis can lead to whole rows being removed from the resultset :(

The implicit sort for UNION also has a relatively huge impact on performance: I tested using a 100K row table and the UNION approach took 21 seconds, while the UNION ALL took only 3 seconds.