[AccessD] Access XP forms bound to ADO recordsets

> I'll keep this and study it.
Hi Dan,

Please try to stress test it "to death" first of all because as I wrote in a
posting to John here there are rumors it forces MS Access to loose
stability...

> Having said all that, when I go to companies and tell them that I develop
> in
> Access, have been doing so for eight years, and show them how my system
> works, the concern over Access drops off.
Good news! I thought MS Access is getting "semi-dead"...

> I am looking forward to what Access 12 will have in it.  I've seen some of
> the descriptive material published by MS, and so far it looks great!
Yes, it does look quite differently and most of the changes are mainly power
users oriented AFAIS - therefore MS Access developers may expect more "messy
software" to fix coming in their direction! :)

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 8:22 PM
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets


> Hi Shamil,
>
> Thank you for this additional information.  I've learned to read your
> information as though I was a student in your class!  I'll keep this and
> study it.
>
> The fact that people are moving away from Access as a development platform
> is interesting.  My experience is that people in IT departments have had
> bad
> experiences with Access.  They've repeatedly seen where someone in their
> company learns how to use Access to some extent to do something that many
> people like.  But because that person doesn't have the ability or time to
> become a real developer, the application has trouble.  Then the IT
> department gets told by a VP that this is a 'must have' application that
> the
> IT department must fix Now!  So, the IT department has to fix it with
> unbudgeted time and unbudgeted funds.  After a few experiences like this,
> IT
> people tend to get leery of Access.
>
> However, with VB or VB.net, a person without actual training in database
> development and connection between a FE and BE, can't even get started.
> (I
> have to admit that when I began developing in Access, I didn't understand
> the concept of FE and BE either.)  This makes (today) the choice of
> developing significant applications using VB.net the 'safe' choice for
> larger companies.
>
> Having said all that, when I go to companies and tell them that I develop
> in
> Access, have been doing so for eight years, and show them how my system
> works, the concern over Access drops off.  They can see for themselves
> that
> I can produce.  I typically market to small to medium sized companies, so
> that may factor in also.
>
> I am looking forward to what Access 12 will have in it.  I've seen some of
> the descriptive material published by MS, and so far it looks great!
>
> 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 6: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