[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/thread/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%3Dima
>
ges%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*.*access.*%26lr%3D%26hl%3Den#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/40e731c75071f8c9
>
?q=Bind+form+to+disconnected+recordset+group:*.*access.*&hl=en&lr=&ie=UTF-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