[AccessD] Access XP forms bound to ADO recordsets

> binding to an ADO recordset, attempts to access RecordsetClone throw an
> error "Type mismatch" on the line of code
>
>        Set mrst = mfrm.RecordsetClone
John,

I didn't get it first, sorry - I thought you're asking about ADODB.Recordset 
cloning using ADODB.Recordset object not MS Access form.

Yes, you're right MS Access form bound to ADODB recordset returns this 
recordset object reference in the case:

Set mrst = mfrm.RecordsetClone

Here is the code to safely differentiate DAO and ADODB recordsets:

Private Function IsDAORecordset(ByRef rrst As Object) As Boolean
On Error GoTo HandleErr
    IsDAORecordset = IsNumeric(rrst.Type)
    Exit Function
HandleErr:
    IsDAORecordset = False
End Function

Be careful to not occasionally use (similarily named and having the same 
quantity of parameters) DAO.Recordsets' methods with ADODB recordsets - 
these are the source of strange floating errors, which sometimes results if 
GPF....

The best would be to test in code, is returned by form's Recordsetclone 
recordset DAO.Recordset or ADODB.Recordset and then fork to the code, which 
was tested for every recordset type using early (compile time) binding. i.e. 
you'd better NOT use late binding for recordset objects' methods (exception 
getting recordset reference from form's Recordsetclone) in your framework to 
not have unpredictable side effects. "Implements" feature could be of good 
use here for effective compile time binding.

I did "fight" with this issue of form's and combo-/list-box recordset object 
type morphing when writing and debugging code for mdb add-ins used with both 
MDB and ADPs. Having been solved it works now for more than a year without 
any troubles in this area.

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 3:43 PM
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/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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com