Binding a Form to a RecordSet created from scratch

[Yves Goetgeluck]

The complexity of the query behind a form's data require that I generate
the data programatically.
I'm trying to create a recordset from scratch, add fields and fill it
with data, and then bind it to a form.
Thus:

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 'does not help
rs.Fields.Append "Field1", adVarChar, 50
rs.Open

'somewhere inside a loop
rs.AddNew
rs.Fields("Field1") = "stuff"
rs.update

'much later, when all data is filled
rs.movefirst 'this does not help
form.recordset = rs

Amazingly, this works part of the way:
I see the correct number of rows in the form, but all the fields show
the contents '#Error'. If the name of the TextBox containing the field
is incorrectly spelled, it shows '#Name?'. So, something is right and
something is wrong. When I click on one of these '#Error' fields, it
gives the message "The Microsoft Jet databse engine could not find the
object '~sq_fZZZ'. Make sure the object exists and that you spell its
name and the path correctly." where ZZZ = the form's name.

All the controls are Locked and the Recordset type in the form is
configured to snapshot, so I would expect Access is not attempting to
read/write into it.
I've seen the KB 281998 ("How to bind Microsoft Access forms to ADO
recordsets") and the KB 227053 ("ACC2000: Forms Based on ADO Recordsets
Are Read-Only"). I note that all their examples start from a rs.open
"select ... while mine is not based on any table.

Is what I'm trying to do even possible? Just dumb luck that it's even
displaying the right number of rows?
How else should I go about creating a temporary table to hold results
for display only, when the underlying calculations are not realisable in
SQL? Should I create tables on the fly, remember their name somewhere,
pass that around and destroy the table on form close?

Thanks for any suggestions,

Yves Goetgeluck

 

[David Mueller]

IMO, much easier to insert your results into a table, and when finished
delete * from the table. I usually delete * before I insert, and when I'm
finished.

You could also do a make table query every time, but I find it easier to
just have a table already in there.

If you have multiple users (or ever will have multiple users), the table
should live in the front end.

 

[Ron Weiner]

Look back in this forum a couple days for the subject "Binding Disconnected
ADO recordsets to forms" for a discussion about this same topic. Then have
a look at
http://groups.google.com/group/comp...ugroup=*.*access.*&lr=&hl=en#f20d79536029060e
for the round-a-bout way to accomplish this. I breiefly tested the solution
in an A2K database with no success. The author aludes to AccessXP in his
example so it may be that this solution is not viable for A2K users. If you
are targeting A02 or A03 then you will probably be OK.

Alas for me, I have many customers that continue to use the Access2K
platform and plan to do so for the foreseeable future. So therefore I am
still using a couple of temp tables to get where I need to go. Hopefully
one day I will be able to avail myself of this superior technique.