[AccessD] Updateable Access Forms Bound to ADO Recordsets

Jim Lawrence accessd at shaw.ca
Sat Dec 23 12:23:59 CST 2006


Hi Dan:

I have worked with similar systems but they have only extended province
wide. One of the most interesting ones connected to 18 offices.

The architecture was created for a number of reasons given a series of
limitations and a fixed budget.

1. The MS SQL database only had 12 connection licenses.
2. Some tables had up to .5 million records
3. It had to be very fast.
4. The FE Access 2000.
5. It had to be deployed from a central location.
6. It had to be able to support up to a hundred simultaneous users.
7. A tech would not be on all sites so any pre-configuration was difficult.
8. On some sites the connections were unstable.

1. The first issue that had to be handled was that there were insufficient
licenses to have all the users directly connected (bound) at all times. So
an unbound structure had to be implemented.
2 and 3. With large volumes of data and the requirements for big reports
with 30 to 100 thousand records, data had to be moved quickly. Using ADO-OLE
allowed data to stream at about 40,000 records per second over high speed
lines. Using pass-through queries were a joke and ODBC connection, among
other considerations was still too slow.
4 and 5. That was the best part as the Access presentation manager allowed
the creation of full featured forms and excellent reports with no additional
applications or expenses. MS Access was of course on every station by
default so the application could be downloaded to any location and would run
immediately.
6. By using an unbound concept, access to the data could be judiciously
controlled. The FE would attach to the server, pull the data and then
disconnect again or connect quickly to make adds, updates and deletions. The
connection process was so fast that users rarely noticed any delays in
service even under heavy load.
7. One of the main problems with ODBC is that the interface required tech
support onsite or through the phone to properly install. Though this is one
of the best connection methods it is slower than ADO-OLE direct connection.
ADO on the other hand is installed and fully operational on all current
Window OS computers... no tech support needed.
8. In summary, ADO-OLE, with unbound forms has very fast data transfer, if a
connection is lost it can be quick established again, with not corruption or
data lost. The only downside is that the initial design requires more work
but subsequent support issues are minor. 

Sorry for the long winded description but I have used these concepts for
over 10 years and my clients once their applications are up and running only
call me back to add new features.... and to attend Christmas parties. (No,
it is not that good but I thought I would throw that in but it has been a
good year.)

Regards
Jim

PS It works great with SQL Express but I have not tried building an
application with Access 2007; yet. If you need any help just give me a call
off-line.       

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Saturday, December 23, 2006 8:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Updateable Access Forms Bound to ADO Recordsets

Hi Jim,

This OLEDB Provider could be used with an mdb as well as an adp (especially
with SQL Server 2005 Express using Management Studio).  Also, I think I read
that Access 2007 can be used as an ADP with SQL Server 2005, but could also
used with SQL Server 2000 in some sort of 'crippled' mode.

All the discussions in this list seem to state that if you're using ADO you
must use unbound forms, and bound forms are recommended for DAO/Jet but
unbound forms could be used.

This OLEDB provider:

	.provider = "Microsoft.Access.OLEDB.10.0"

describes using bound forms with an ADO recordset.  

I have a currently working mdb database using DAO/Jet in one location that
my client wants to have installed at various plants in North America.  All
these plants are on the same WAN, so a switch to ADO is completely feasible.
But, my system has about 25,000 lines of code where all forms are bound.  If
I can use essentially the same system connected to ADO, I can switch over
with a minimum of code rewriting and become much more valuable to them in
the long term.

My plan is to use Access 2003 (mdb) with SQL Server 2005 Express with
Management Studio Express using this provider.

Dan Waters

-----Original Message-----
Subject: Re: [AccessD] Updateable Access Forms Bound to ADO Recordsets

Hi Dan:

I have done most of my Access work with direct ADO-OLE connecting to MS SQL
and Oracle databases since 1997. Performance increases are on another
magnitude. 

Some jobs that would take an hour could be reduced to seconds by not using
pass-through queries, ODBC drivers... and never using DAO MDB databases
except to stash temporary local data.

Note; that the MS article is describing an ADP type database and as far as I
know that technology has a tenuous future... though my knowledge on its
survival may not be current or complete and some other member may have
further comment.

HTH
Jim

PS I have rarely used bound forms     

-----Original Message-----
Subject: [AccessD] Updateable Access Forms Bound to ADO Recordsets

MS has published a seven page KB article on using updateable Access forms
bound to an ADO recordset.  This uses a specific OLEDB Provider with A2002
or A2003.  The article discusses requirements for SQL Server, Oracle, and
ODBC databases.

Has anyone tried this or have any thoughts on what the performance might be
on a WAN?

http://support.microsoft.com/kb/281998


Thanks!
Dan Waters


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list