How to bind a disconnected ADO recordset to an Access form?

[Florian Esser]

Hi all,

what I've been trying to accomplish for a couple of days now is to bind an
Access form to a disconnected ADO recordset, i.e. a recordset that was not
populated by a query/table. Here's an example:

== begin code =======

Dim rs As New ADODB.Recordset

With rs
.Fields.Append "id", adInteger ' Add one field to the
recordset
.Open
.AddNew ' add a record
rs!id = 123
.Update
End With

Set Me.Recordset = rs ' code is in a form, so Me
refers to the form

== end code ========

So far, so bad. Although Access doesn't report an error when assigning rs to
its Recordset property, the result is not at all what you would expect. If I
put e.g. a textbox on the form and bind it to "id", all it display is
"#Error". If I debug the code and try to display the textbox's value with a
MsgBox command (MsgBox Text0.Value) an error pops up that says, that Access
cannot find table '' [empty string]. So what I guess is that this is a
general flaw in Access (and yes, still in Access 2003) and forms cannot get
data from recordset which are not related to a table or query in the
database. If that is indeed so, it's a shame because it takes away what is a
remarkable advantage of ADO over DAO. Or do I make a fatal mistake myself?

Another interesting side note: If I bind the disconnected recordset to,
let's say, a list box, the list box displays the right number of rows (i.e.
rs.RecordCount rows). However, all column values are Null. So the list box
gets everything right except for the actual field values.

Opinions appreciated.

--Florian

====================================================================
Please do not reply to this message directly, since all e-mails to the given
address will be discarded.
If you need to contact me directly, send your mail to "florian DOT esser AT
gmx DOT de"

 

[Florian Esser]

Well, it seems, that I have found a solution, though it is not exactly what
I wanted, but hey.

So, all that have the same problem, consider the following:

== begin code ======================

Dim rs As New ADODB.Recordset
Dim s As New ADODB.Stream

s.Open

rs.Fields.Append "id", adInteger, , adFldRowID
rs.Open , , adOpenStatic
rs.AddNew
rs!id = 123
rs.Update
rs.Save s, adPersistXML
Set rs = New ADODB.Recordset
rs.Open s, , adOpenStatic, adLockReadOnly

Set Me.Recordset = rs

== end code ========================

This does the trick. The form now displays the records correctly. The only
drawback is, that I had to reopen the recordset static and read-only, so I
won't be able to add new records to it with my form. It seems that if you
want to bind an updatable recordset to a form, it has to be based on a
query/table.