Binding Disconnected ADO recordsets to forms

[Guest]

I have been trying to bing disconnected ado recordsets to forms with no
success.
I have also found several tricks that address that issue, however they also
did not work.

Has anyone sucessfully has bound a disconnected recordset (created by logic
tier of code) to a form ?

Sincerely,
Remi Szyndler

 

[Ron Weiner]

I do not think this is possible in an MDB as a forms default recordset is a
DAO recordset. I fooled around with this about 4 months ago for a couple
days, but I was never been able to bind a disconnected recordset to a form
at all, and even when I left the recordset connected the form became read
only. I have not tried any of this with Access ADB's, but it might be
possible there.

I wound up using a temp table (ugh!) instead of a disconnected recordset to
get my project moving forward. Please do get beck to us here should you
find a solution. Disconnected recordsets would be a sweet addition to
Access.

 

[MacDermott]

It's my understanding that while disconnected ADO recordsets can be bound to
Access forms, they are not updatable, which is often why we'd want to bind
them in the first place. I think I've been told that the incompatibilities
are way in the deep crevices of Access and ADO, where we mere mortals don't
tend to tread...

 

[Ron Weiner]

Here is a simple implementation of attempting to bind an Access MDB form to
a disconnected ADO recordset.

Option Compare Database
Option Explicit
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Dim strSql As String

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strSql = "Select * From MyTable"
rs.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.ActiveConnection = Nothing
Set Me.Recordset = rs ' Dies Right here with a error 7965
' The object your entered is not
a valid Recordset property.
End Sub

Private Sub Form_Unload(Cancel As Integer)
rs.Close
Set rs = Nothing
End Sub

If you rem out "rs.ActiveConnection = Nothing" line then the form is bound
to the recordset BUT everything is Read Only. I have attempted just about
every combination of Cursor Type and Lock Type imaginable to no avail. If
someone has other code that behaves differently I'd love to see it.

 

[Sylvain Lafontaine]

Here are some informations that might be relevant on this topic:

http://support.microsoft.com/kb/227053/EN-US/

http://support.microsoft.com/?kbid=281998

 

[Guest]

http://groups.google.com/group/comp...ugroup=*.*access.*&lr=&hl=en#f20d79536029060e


This guy did the homework for us. It is cumbersome but I was able to use
fully fabricated (created by code) ADO recordsets bound to a form.

 

[Guest]

It is unfortunate that such a robust product as Access does not provide a
simple way to use fabricated data sets as sources.. does anyone.. including
the heavy heads on the MS side have anything to contribute to this discussion
?

I find listboxes working off strings to be Very SLoooow, something that
works MUCH FASTER with a recordset versus a string, but yet again I face the
same problem as with FORMS where a fabricated recordset does not WORK !!!

C'mon guys - if someone is trying to adhere to the three tier application
architecture the person is bound to use fully disconnected data traveling
between layers and yet we have this horrible MS stumbling block.

Any replies appreciated. I need time to write code for what the app needs to
do , not handling problems with access object binding !

Sincerely,
Remi Szyndler

 

[Brendan Reynolds]

Access is not designed for developing three or n-tier applications. If your
application really needs to be a three or n-tier application (most don't)
then you would be better advised to use tools designed for that purpose.

 

[Ron Weiner]

Really Cool idea, but alas I was unable to get it to work with an Access 2K
database which is my current target audience baseline database. I get and
error 7965 right at the "Set Me.Recordset = mrsTest" line with the message
"The object you entered is not a valid Recordset property". I will have to
look at this closer at a latter time, as my A-- is in a sling for a deadline
right now. Thanks for sharing