Dan Waters
dwaters at usinternet.com
Sat Mar 11 17:27:23 CST 2006
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.)
----- Original Message -----
From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Saturday, March 11, 2006 3:36 AM
Subject: Re: [AccessD] Access XP forms bound to ADO recordsets
> John,
>
> I did try ADODB disconnected recordsets with MS Access XP/2003 MDBs and
> they
> seems to be working rather well.
>
> That is a surprise here because the book "Microsoft Access Developer's
> Guide
> to SQL Server", which I read before states this can't be done. But the
> book
> I have is for MS Access 2000/MS SQL 2000 - and for MS Access 2000 this
> feature doesn't work. I don't have a new edition of the book - if it
> exists
> the authors may have written about this new useful feature....
>
> I hope I'm not mistaken - I did recheck many times - yes, it seems to
> work!
> Please verify....
>
> In P.S. of this message there is a sample code, which basicaly works OK
> but
> which needs more investigations - it doesn't yet process all the use
> cases.
> This code is for [authors] table from [pubs] sample database. It can work
> with both MS SQL and MS Access BEs use:
>
> - BindFormToSQLADORecordset - to connect to MS SQL BE and
>
> - BindFormToJetADORecordset to connect to MS Access BE.
>
> I did check with MS SQL profiler - communication with MS SQL BE is minimal
> and connection is used only when database operation is in progress - i.e.
> on
> short period of time for properly organized communication with BE
> database.
>
> If additional investigations/testing will show that this simple method
> works
> for all the required use cases then that's "what doctor ordered." - I mean
> you can relax and happily continue staying in your comfortable "bounders
> camp" for quite some time... (Although I'd recommend to use GUIDs for PKs)
>
> It would be also very useful to make it working in disconnected optimistic
> batch update mode but I doubt it's possible - I'd be happy somebody will
> present here a sample how similar simple method can be used in
> disconnected
> optimistic batch mode.
>
> Shamil
>
<<< tail skipped >>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com