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