[AccessD] Access XP forms bound to ADO recordsets

John Colby jwcolby at ColbyConsulting.com
Mon Mar 13 12:23:27 CST 2006


Charlotte,

In fact, when the form binds to an ADO recordset, RecordsetClone "becomes"
an ADO recordset.  I dimmed an object and set the object to the
RecordsetClone, and the resulting object has the properties / methods of an
ADO recordset.  I did not try dimming an ADODB.Recordset and setting that to
the RecordsetClone, but I will try it.  It appears that Access morphs
RecordsetClone to whatever recordset type is required.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Monday, March 13, 2006 12:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets

John,
RecordsetClone is DAO.  With ADO, you can clone a recordset, but it is a new
recordset and can't be used the way you use recordsetclone. 


Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Monday, March 13, 2006 4:43 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets

What I am asking though is whether RecordsetClone still exists, and is
somehow populated from whatever data source the form is bound to?  After
binding to an ADO recordset, attempts to access RecordsetClone throw an
error "Type mismatch" on the line of code

        Set mrst = mfrm.RecordsetClone

Mrst is defined in the class header as a DAO recordset.

I might be able to get away with generalizing mrst to an object.  

NOTE: I tried that, and while the "Set" now works, the objects have
different methods so any code using the object has to have different code
paths written.  So apparently RecordsetClone is indeed initialized as an ADO
recordset which makes all of my DAO specific code (FindFirst for example)
throw a runtime error if encountered.  If I am going to do this, I will need
to branch my code based on the Access Version as well as whether I actually
used the ADO recordset code.

>Please do not spend a lot of time on it now or at least do not plan to
broadly use it in your development until

Luckily, in my framework, I can turn on and off capabilities by examining
SysVars.  It does sound like the instability objections were from an early
adopter.  Like most things in Software, trying to use any specific feature
in Access in the early stages of its introduction can be risky.  It is
possible (and hopefully true) that they found and fixed the bugs causing
such instability.  One of the problems with influential early adopters is
that people read their warnings and never use a feature because they
reported it unstable.  If they never go back to revisit it, then it is
forever labeled unstable.

Perhaps it is time for other influentials (you and I and this list) to
revisit this feature and see if it can be used now?

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Monday, March 13, 2006 3:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets

> Does RecordsetClone become invalid?
No, it doesn't, John.
But it has another name.
Similar method for ADODB recordsets is called Clone.

> I am looking at this now.
Please do not spend a lot of time on it now or at least do not plan to
broadly use it in your development until it's very well tested and its
limitations are clearly defined - there are rumors this feature introduce
instability in MS Access work. I have doubts in that because I haven't seen
any time MS Access crashes when bound to disconnected ADODB recordsets the
way I described.

On the other hand I did do "fight" in my work in the past with MS Access
GPFs, when listboxes/comboboxes were bound to ADODB recordsets but as far as
I have found this wasn't the problem with ADODB recordsets but the problem
that in that case late bound DAO or ADODB recordets were used and these
latter were opened in library mdb and there was a bug when DAO recordsets
were used for ADP's forms and that was the cause of the floating GPF trouble
or something like that. WIth this problem fixed the applications work now
well for more than a year.

Shamil

----- Original Message -----
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Monday, March 13, 2006 8:15 AM
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets


> Shamil,
>
> I am looking at this now. Do you know if binding to an ADO recordset 
> changes the data type of the RecordsetClone.  I was under the 
> impression that RecordsetClone was always a DAO recordset.  Does 
> RecordsetClone become invalid?  I use RecordsetClone to perform many 
> different operations, including syncing the form to a combo and VV.
>
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil 
> Salakhetdinov
> Sent: Saturday, March 11, 2006 7:12 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
>
> Dan,
>
> Yes, I have seen that - in fact I did do google before I started my 
> experimenting.
> In KB281998 "How to bind Microsoft Access forms to ADO recordsets"
> http://support.microsoft.com/kb/281998 they talk AFAIS about binding
to
> connected ADODB recordsets.
>
> The original question (as I got it) was hwo to bind to disconnected
ADODB
> recordsets and then implied question is: is there a simple and generic
way
> to translate/transfer changes from disconnected recordset to the 
> backend...
>
> As it happens there is a trick to do that described in my previous 
> posting.
>
> I did do google now additionally and I did find that the similar trick
is
> described already several years ago. They do it a little bit
differently 
> but
> the principle is the same - they write:
>
> <<<
> Now, the clever tricks:
>
> 1.  Open a static, optimistic batch recordset, disconnect it, save it
to a
> simple in-memory stream object, set the stream position to zero so it
can 
> be
> read from the beginning, and open a new copy of the recordset from the 
> stream.  Now, bind one copy to the form, and start working.  When
you're
> ready to do a batch update, first compare the 2 recordsets for
changes, 
> and
> apply any adds, changes, and deletes to the unbound recordset, then
run
> BatchUpdate from the unbound recordset.  Note that Access sets values
of
> IDENTITY columns to zero, so that's an easy way to filter for added 
> records.
> ...
>>>>
>
> This excerpt is from this posting (watch line wraps!):
>
> <<<
> ADP: A2K vs 2002?
> From:  Steve Jorgensen
> Date:  Sun, Jul 21 2002 7:29 pm
>
>
http://groups.google.com/group/comp.databases.ms-access/browse_frm/threa
d/17
>
53dbca8189eb3/f20d79536029060e?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=1&prev=/
grou
>
ps%3Fas_q%3DBind%2520form%2520to%2520disconnected%2520recordset%26safe%3
Dima
>
ges%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*.*access.*%26lr%3D%26hl%3De
n#f2
> 0d79536029060e
>>>>
>
> And here is another related information:
>
> <<<
> From:  Steve Jorgensen
> Date:  Thurs, Nov 28 2002 11:45 pm
>
>
http://groups.google.com/group/comp.databases.ms-access/msg/40e731c75071
f8c9
>
?q=Bind+form+to+disconnected+recordset+group:*.*access.*&hl=en&lr=&ie=UT
F-8&
> oe=UTF-8&rnum=2
>>>>
>
> So they approve that my trick is legal and is a kind of "wheel 
> reinwention"
> - the difference of my "wheel" is that I do trasnslate changes to the 
> second disconected recordset when form events fire - the disadvantage 
> of my method is that it needs more tricky coding, which still needs to 
> be well
tested,
> the advantage - is that as soons as the changes on the current row are

> done
> I can serialize second disconnected recordset to xml file and in the
case 
> of
> client app's failure batch changes will not be lost etc.
>
> Strange that such a valuable information first time published in
summer 
> 2002
> about using ADODB disconnected recordsets is so rarely discussed on 
> Internet
> - everybody in this World is abandoning "MS Access toy" and switiching
to
> .NET development?
>
> But this feature properly applied makes MS Access FEs good candidates
to 
> be
> used as true FEs - without any significant performance/locking hits on
BEs

> I
> mean....
>
> Shamil
>
> ----- Original Message -----
> From: "Dan Waters" <dwaters at usinternet.com>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Sunday, March 12, 2006 2:27 AM
> Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
>
>
>> Shamil,
>>
>> I've been reading you post with interest.  Have you looked at MS KB 
>> 281998?
>> This gives a method for an Access form to connect to SQL Server, Jet,
and
>> Oracle BE's and be updateable.  They identify a specific provider to
do
>> this
>> that can be used to set up bound forms.
>>
>> The provider is:  "Microsoft.Access.OLEDB.10.0"
>>
>> I have not tried this, but thought you might be interested.
>>
>> Dan Waters
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil 
>> Salakhetdinov
>> Sent: Saturday, March 11, 2006 5:04 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
>>
>> John at all,
>>
>> I did make some more experimenting and it happens that MS Access 2003

>> (and
>> probably XP) and ADO 2.8 (and 2.7 also?)  can work in bound mode with 
>> disconnected updatable ADODB recordsets with both MS Access and MS
SQL
>> backends.
>>
>> I did use Provider=Microsoft.Jet.OLEDB.4.0 to connect to MS Access 
>> backend and Provider=SQLOLEDB.1 to connect to MS SQL 2000 backend.
>>
>> Some more testing/experimenting is needed but I did do quite some
tests
>> and
>> they worked OK.
>>
>> As I wrote already this feature is new for me and a (big) surprise -
this
>> is
>> a functionality I have been waiting for 8+ years since 1997 when we
made
>> here a custom framework to work with MS Access and MS SQL in
disconnected
>> mode, but it was rather heavyweight and because of the bugs in MS
Access 
>> I
>> decided to not make it broadly available....
>>
>> Basically this means that MS Access forms can be used in bound mode
with
>> very low performance hit to the MS Access and MS SQL backends - the
same
>> way
>> ADODB disconnected recordsets are used in n-tier applications.
>>
>> The programming of form's events processing is relatively simple and
it
>> should be possible to generalize it to make a simple lightweight 
>> framework in case back-end database(s) follow systematic naming 
>> conventions and database models design principles.
>>
>> The "trick" is to open two ADODB recordsets:
>>
>> - one with batch update enabled
>> - second without batch update
>>
>> and make them disconnected.
>>
>> Then bind MS Access form to the second recordset (without batch
update
>> enabled).
>>
>> (I did experiemnet with recordsets having several source rows as a 
>> generic case to see are there any chances to use MS Access subforms 
>> in bound editable mode with disconnected ADODB recordsets)
>>
>> When MS Access form fires insert, update, delete events all the
changes
>> can
>> be easily translated from the second recordset (where these changes
are
>> made
>> "automagically" because MS Access form is bound to it) to the first 
>> recordset.
>>
>> On form close(or using special button) first recordset can be
reconnected
>> to
>> the backend and batch update applied. (Of course in the case of
special
>> button to apply batch changes the two disconnected recordsets have to
be
>> resynchronized after updates).
>>
>> etc.
>>
>> Here is how "automagically" generated by ADODB batch update looks in
the
>> case of MS SQL backend (Info obtained from MS SQL profiler, line
breaks
>> are
>> inserted manually to make it readable):
>>
>> exec sp_executesql
>>
>> N'UPDATE "Northwind".."Categories" SET
>> "CategoryID"=@P1,"CategoryName"=@P2,"Description"=@P3 WHERE
>> "CategoryID"=@P4
>> AND "CategoryName"=@P5;
>>
>> DELETE FROM "Northwind".."Categories" WHERE "CategoryID"=@P6 AND 
>> "CategoryName"=@P7;
>>
>> UPDATE "Northwind".."Categories" SET
>> "CategoryID"=@P8,"CategoryName"=@P9,"Description"=@P10 WHERE
>> "CategoryID"=@P11 AND "CategoryName"=@P12;
>>
>> INSERT INTO "Northwind".."Categories"
>> ("CategoryID","CategoryName","Description") VALUES (@P13, at P14, at P15)',
>>
>> N'@P1 int, at P2 nvarchar(15), at P3 ntext, at P4 int, at P5 nvarchar(15), at P6
int, at P7
>> nvarchar(15), at P8 int, at P9 nvarchar(15), at P10 ntext, at P11 int, at P12
>> nvarchar(15), at P13 int, at P14 nvarchar(15), at P15 ntext',
>>
>> 99, N'CatName', N'Updated category description', 99, N'CatName', 66, 
>> N'DelCatName', 991, N'CatName2N', N'Updated dscr', 991, N'CatName2', 
>> 662, N'newCatName', N'new Cat description'
>>
>> Live connection to the back-end is needed only on opening of the
source
>> recordsets and on applying batch changes. Therefore connections can
be
>> pooled/should be possible to pool (I didn't experiment here) and to
share
>> them between several (many) front-end applications/clients if these 
>> connections are opened using ActiveX DCOM servers running on server 
>> PCs/.NET
>> Remoting(?) objects. As well as actual disconnected recordset
creation 
>> and
>> reconnection and backend update can be executed on (Web) server side
-
>> depending on whatever is used on backend....
>>
>> Yes, I know dynamic SPs accessing database tables directly do not
look
>> nice
>> but there could be many ways to make it working differently using 
>> predefined stored procedures (e.g. by serializing ADODB recordset 
>> into XML
file(s)
>> where all the updates (inserts, deletes, updates) are coded special 
>> easily parsed way and then reformat these XML files into the calls to 
>> the predefined stored procedures etc.etc.
>>
>> Here is how it looks this saved XML(no direct correspondence with 
>> presented above batch T-SQL update) :
>>
>> - <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
>> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
>> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
>> - <s:Schema id="RowsetSchema">
>> - <s:ElementType name="row" content="eltOnly" rs:updatable="true">
>> - <s:AttributeType name="CategoryID" rs:number="1"
rs:writeunknown="true"
>> rs:basecatalog="Northwind" rs:basetable="Categories1"
>> rs:basecolumn="CategoryID">
>>  <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
>> rs:fixedlength="true" rs:maybenull="false" />  </s:AttributeType>
>> - <s:AttributeType name="CategoryName" rs:number="2"
>> rs:writeunknown="true"
>> rs:basecatalog="Northwind" rs:basetable="Categories1"
>> rs:basecolumn="CategoryName">
>>  <s:datatype dt:type="string" dt:maxLength="15" rs:maybenull="false"
/>
>>  </s:AttributeType>
>> - <s:AttributeType name="Description" rs:number="3"
rs:nullable="true"
>> rs:writeunknown="true" rs:basecatalog="Northwind"
>> rs:basetable="Categories1"
>> rs:basecolumn="Description">
>>  <s:datatype dt:type="string" dt:maxLength="1073741823"
rs:long="true" />
>>  </s:AttributeType>
>>  <s:extends type="rs:rowbase" />
>>  </s:ElementType>
>>  </s:Schema>
>> - <rs:data>
>>  <z:row CategoryID="1" CategoryName="Beverages" Description="Soft
drinks,
>> coffees, teas, beers, and ales" />
>>  <z:row CategoryID="2" CategoryName="Condiments" Description="Sweet
and
>> savory sauces, relishes, spreads, and seasonings" />  <z:row 
>> CategoryID="3" CategoryName="Confections"
Description="Desserts,
>> candies, and sweet breads" />
>>  <z:row CategoryID="4" CategoryName="Dairy Products" 
>> Description="Cheeses"
>> />
>>  <z:row CategoryID="5" CategoryName="Grains/Cereals"
Description="Breads,
>> crackers, pasta, and cereal" />
>>  <z:row CategoryID="6" CategoryName="Meat/Poultry"
Description="Prepared
>> meats" />
>>  <z:row CategoryID="7" CategoryName="Produce" Description="Dried
fruit 
>> and
>> bean curd" />
>>  <z:row CategoryID="8" CategoryName="Seafood" Description="Seaweed
and
>> fish" />
>> - <rs:update>
>> - <rs:original>
>>  <z:row CategoryID="99" CategoryName="Update me" Description="Update
me -
>> OK" />
>>  </rs:original>
>>  <z:row CategoryID="99" CategoryName="Update me" Description="Update
me -
>> OK1" />
>>  </rs:update>
>> - <rs:update>
>> - <rs:original>
>>  <z:row CategoryID="991" CategoryName="Update me2"
Description="Update
>> me2 - OK" />
>>  </rs:original>
>>  <z:row CategoryID="991" CategoryName="Update me2"
Description="Update
>> me2 - OK1" />
>>  </rs:update>
>>  <z:row CategoryID="661" CategoryName="Inserted2"
Description="Inserted2"
>> />
>> - <rs:insert>
>>  <z:row CategoryID="662" CategoryName="Inserted3"
Description="inserted 
>> 3"
>> />
>>  </rs:insert>
>> - <rs:delete>
>>  <z:row CategoryID="66" CategoryName="DeleteMe" Description="Deleted"
/>
>>  </rs:delete>
>>  </rs:data>
>>  </xml>
>>
>> I do not post code here now because I wanted to experiment more and 
>> because it would be useful if somebody else tries to repeat the 
>> "trick"
described
>> above - if it will work for them then a whole set of new
opportunities to
>> use MS Access as a real development tool will be opened....
>>
>> I will post code after additional tests within one-two weeks when it
will
>> become clear that it works.
>>
>> Please post any links here if you think that my posted information
looks
>> like "wheel reinvention" or you think it doesn't have practical 
>> application because of this or that several limitations etc.etc.
>>
>> Thank you.
>>
>> Shamil
>>
>> P.S. I have installed on my PC:
>>
>> - MS Windows 2003 Server SP1
>> - MS Office 2003/MS Access 2003 SP1
>> - MS SQL 2000 SP3a
>> - MDAC 2.8
>> - NET Framework 1.1 and 2.0
>> - Visual Studio 2003 and 2005
>> - I have also MS SQL Server 2005 Express edition but I didn't
experiment
>> with it
>>
>> P. P.S. Yes, I didn't talk about identity(autonumber) fields - I 
>> generally use GUIDs for PKs.
>> To properly manage numeric identity values some more tricks are
needed -
>> I'd
>> be grateful if somebody post information on this subject 
>> (updategram/diffgram XML is a direct solution here probably - and 
>> serialized/saved ADODB batch update can be converted into diffgrams
by
>> using
>> XSLT or VB(A) or VB.NET/C#/C++ code etc.)
>>
>
> <<< tail skipped >>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list