Steve Conklin
developer at ultradnt.com
Mon Mar 13 09:36:13 CST 2006
I have been following this thread with great interest. When faced with this
issue, I have gone completely the opposite direction - that is not bleeding
edge but back to Access basics. This was in an Access FE, MSSQL BE.
Doing unbound in a "header" record (invoice, for instance) is pretty
straightforward, especially when starting with a bound form and then
deleting the record source property, so all controls have the same name as
fields. The details in the subform (invoice line-items) are another matter.
After several failed attempts at a disconnected ADO recordset, I thought
about arrays, dictionaries, collections, converting somehow ADO to DOA at
runtime, but finally came back to Access iteslf - I had the best device ever
created for holding columns and rows of data:
Access' own Tables!
They provide nearly code-free storage, easy creation of new rows, are easily
displayed in bound sub forms, etc.
I appended all the tbl_invoice_details to tbl_invoice_details_local - which
has the exact same structure, except that the pk-autonumber field in the
local is neither. It is a long with a default of 0 (indicates new when
writing back). Also added a y/n field to indicate "deleted" (sub form
filters for deleted=0). This is decidedly low-tech as compared to
serializing XML streams and such, and also requires home-grown locking (I
added a bit field to invoice to indicate "in use"), but it does work by
virtue of staying in Access sweet-spot rather than venturing in to uncharted
waters.
Steve
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Monday, March 13, 2006 10:00 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
> Given the size of the issue there, they would be a perfect test site
> for this.
OK, I will publish the code and the crash test tomorrow and then it can be
reused I think for your real application.
Best regards,
Shamil
--
Web: http://smsconsulting.spb.ru/shamil_s
----- 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 4:48 PM
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
> >Please do not spend a lot of time on it now or at least do not plan to
> broadly use it
>
> I have one specific place that I want to use this, the form that is
> causing
> locking issues at Invo Health Care. Given the size of the issue there,
> they
> would be a perfect test site for this.
>
>
> 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com