Shamil Salakhetdinov
shamil at users.mns.ru
Mon Mar 13 13:04:56 CST 2006
> Have you tried using an unbound form with
> a disconnected recordset?
Charlotte,
Yes, bind a form to ADODB even CONNECTED recordsets works - it's described
here - http://support.microsoft.com/kb/281998.
Binding a form to ADODB DISCONNECTED recordset also works but changes can't
be submitted to BE without "clever tricks", which were mentioned here.
And these "clever tricks" as Steve Jorgensen found several years ago put "MS
Access on the knees" - in fact kill it completely often without even any
error message - in the case VBA code is used to set values of MS Access
forms bound to disconnected ADODB recordsets when these recordsets are
created from MS SQL back-end data.
I think I have found a workaround, which lets to bypass this limitation. I'm
testing it now here. I will post the source code for this "crash" test
tomorrow. So far there is "some light in the end of the tunnel" - I did run
in total ~50000 forms' openings, data changes VIA CONTROLS, back-end
updates, form closing for four different forms of Northwind sample app, all
bound to disconnected ADODB recordsets getting opened/closed automatically
in five running on one PC MS Access mdbs, two of which were talking to MS
SQL BE and three of which were talking to MS Access BE....
The results are promising - the applications DID NOT CRASH.
But they stopped in the end with strange error message:
<+
Run-time error '3011'.
The Microsoft Jet database engine could not find the object ''. Make sure
the object exists and that you spell its name and the path name correctly.
->
This error message was raised for DoCmd.Openform with legal parameters etc.
I have to make some more fine grained tests and run them more before
publishing them to not involve you and others into meaningless adventure...
Shamil
----- Original Message -----
From: "Charlotte Foust" <cfoust at infostatsystems.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, March 13, 2006 8:11 PM
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
> Shamil,
> I'm just dipping a toe in here quickly before I get back to work. Have
> you tried using an unbound form with a disconnected recordset? I swear
> I've done this, but I'll have to look for a demo.
>
>
> Charlotte Foust
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
> Salakhetdinov
> Sent: Saturday, March 11, 2006 4: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/1753dbca8189eb3/f20d79536029060e?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=1&pr
> ev=/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/40e731c75071
> f8c9?q=Bind+form+to+disconnected+recordset+group:*.*access.*&hl=en&lr=&i
> e=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