Jim Lawrence
accessd at shaw.ca
Fri Jul 9 10:21:23 CDT 2010
MS Access has a ODBC method that is links and uses a pass-through technique to connect to the data source... Now I must admit I am not an expert with bound or pass-through methods for connecting with FE/BE... (The last time I attempted to work with it it was over 10 years ago...and I must be honest with you that I have tried to forget experience.) When it comes to running remote users, I have traditionally used a bare form, report, list, combo and applied a recordset source upon open/load. To speed the whole operation up a connection with the MS SQL server is established upon login. There are no local queries as they are all at the BE as SPs...this of course is also for speed. A repeatedly used query is optimized on the server and then cached. The only data that is kept locally is the positioning data (IE which record of the form or list has focus?) Then there is static or semi-static data (IE a city list or name list) Forms are populated in-real time. In otherward, the instance the users navigates to a record a request for new data to fill the record is made, the server gets the request, a local recordset is filled and the form is populated. (This of course is also for speed as a small amount of data can transferred so fast that a user moving from one record to another should notice no delays) As long as the connection between the SQL and the recordset is maintained the data is effectively synced and can be resyned. I used have to do the resyncing manually (via a method called asnchronous notification... in otherwards tell me if any of the data I am working with has changed on the server) but I understand that the current ADO even has a method, similar to AJAX which can will quitely resync, in background on a field by field bases. (My ADO bible is 10 years out of date, a little ragged and with about 1000 pages and still there is a lot I do not know about the API.) In a network or remote environment only keep the connection open to recordset (persistant) of the specific record and only when it is being worked on (sync at the instance a record is being added or updated) Aside: In most system, I never delete records, just flip a flag and force a refresh, recount and reposition. Close all connections when viewing records for example or after populating a list etc. Connections cost, in time, performance and you have to pay for number of connections your SQL Server... unless you have bought the the unlimited license package. Usually, I have found, if properly programmed, 20 connection can do a hundred users but 10 is a minimum. In summary, ADO is the precursor to all the data connection functionality now built into the .Net apps. The .Net applications have not improved the automation and in fact to create a stellar app it still requires a lot of grunt work especially if you are in an environment of multi-users, multi-data sources with a mixture of local and remote requirements. This is just nudge of the proverbial iceberg. HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, July 08, 2010 9:23 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Terrible performance like I have never seen before How do you do that though? If you go to linked tables, those are ODBC correct? John W. Colby www.ColbyConsulting.com Jim Lawrence wrote: > Now that we are on the subject of connection databases and I am not > interested in the bound or unbound discussion, but in defense of ADO-OLE > over ODBC. > > A little preamble; 2001 and the company I was worked for crashed and burned > but I bid on a excellent project of connecting Oracle to 80-90 desktops > worth up go 100k. I used the standard ODBC connection. Performance was > acceptable for ADD and UPDATE but SELECT was so slow... Needless to say, no > excuses, I lost the contract. I fortunately, I got a second chance, with > another department and this time, after a bit of research won the contract. > The contract was for a F/T programming project which extended for almost 3 > years. This time I used ADO-OLE and the performance was superior to any > application out there... nearly twice as fast as Oracle's own desktop > product. > > ADO is one of the most under-rated connection products. It is free and comes > on all Windows OS since Win95, requires no installs as everyone, no matter > how far away, has it. There is none of this going from one desktop to > another to set things up. ADO also has a lot of built-in functionality; it > has the Shape object that works like a Transpose matrix... great for super > fast cross-tabs. Then the Stream object for transferring pictures, documents > and video (though I never tried the streaming video in a client's app)... at > the time it was fastest streaming method available and it could make your > network look like it had a Flash Server. ADO synchronization so that data > could be coming for multiple sources and be 'unioned' together... great for > cross-referencing data. There are many other objects that very impressive > and again they are very lean and very fast. > > I must admit that ODBC is initially easier to setup but with a big or > long-term project ADO is superior in every way. > > Jim > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan > Sent: Thursday, July 08, 2010 2:31 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Terrible performance like I have never seen before > > Alternatively, just upsize the data, link to the SQL Server tables by ODBC > and carry on as > usual for a start. Then you can work your way through the new system > upgrading a piece at > a time. > > I don't want to get the bound/unbound arguments stirred up again, but make > sure that the > gains are worth it before you go to the dark side. :-) > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com