[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/1753dbca8189eb3/f20d79536029060e?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=1&prev=/groups%3Fas_q%3DBind%2520form%2520to%2520disconnected%2520recordset%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*.*access.*%26lr%3D%26hl%3Den#f20d79536029060e
>>>

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 >>>