 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / December 2005Official Status of SQLServer 2005 ADP
I just started reading this group and disapointed to read that it sounds like ADPs are being hung out to dry. I was not impressed by this. ADPs would allow someone with a bunch of Access programming background (like me) to leverage that knowledge to work with a SQL Server backend, without learning a new environment. If you program in Access you relize there is no faster way to built an application. If it is true that going forward less and less functionality, it could be hard to justify new development with ADP Those that have implemented (or plan to) these solutions may have an interesting support problem going forward. However everything I have read is not 'Official' does anyone know the status of this, or can you point me in the right direction. Thanks Terry Mc Terry MS will come out with a patch on this; from what i've heard. I mean-- real simple math; microsoft-- server side crosstab query wizard come on plz plz plz The Access MDB with linked tables is a superior tool to access SQL Server, Oracle, DB2, etc. backends. The ADP was extremely limited in its functionality which is why it never gained wide acceptance. It could only link to SQL Server tables. It couldn't even link to Access/Jet tables!!! It also required significantly different techniques to develop applications such that about the only things an MDB and an ADP have in common is they are both Access and they both use VBA. My understanding at the moment is that future development of the ADP will be limited or non-existent. In fact, with Office 12 which will be released some time near the end of 2006, the ADP will not be enhanced to allow it to create tables in SQL Server 2005. It will be able to access tables from 2005 but not create them. So, if you need to use the ADP to create databases and manage them, you have to stick with SQL Server 2000. Microsoft plans to continue support for existing ADP but will no longer support creating them. I don't know what will happen with the upsizing wizard. I hope it stays so it can be used to create the SQL Server database. Otherwise, you would need to create the database from scratch or use a tool such as erWin to translate the Access/Jet schema to SQL Server, Oracle, DB2, etc. >I just started reading this group and disapointed to read that it sounds >like ADPs are being hung out to dry. [quoted text clipped - 17 lines] > > Terry Mc > My understanding at the moment is that future development > of the ADP will be limited or non-existent Do you have any public source for this ? Thanks --alexT Excerpt for the integration of the new GUI, the bits for ADP2003 were exactly the same as for ADP2000 and all of the known bugs were still there. Since then, practically none of these bugs seems to have been corrupted in the subsequent service packs for Office 2002 or 2003. In fact, it's the contrary: the latest service pack (SR2) for Windows XP seems to have brought new bugs for ADP2002 (but none for ADP2003) while Win2003 seems to offer new troubles for both ADP 2002 and 2003. It's fairly obvious that much of the new high-level developments in the database domains will be done for the .NET Framework only; while the next version of Access will probably bring new features for entry level design of databases (better wizards/designers for beginners, etc.)  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >> My understanding at the moment is that future development >> of the ADP will be limited or non-existent [quoted text clipped - 4 lines] > > --alexT As we started out looking at writing a replacement for the old Access app that basically runs our small business, I wrestled with the idea of writing it with Access (maybe ADP) or VB.Net. Access I know and love, .NET I have played with, but that's about all. I had decided on using Access because I knew we could get 80% of the functionality we wanted in no time, plus programing is not my primary job in our company. We planed on doing some more advanced stuff with .NET (outside of the main app) once the nuts and bolts were taken care of. I am rethinking this now. I imagine we will always have Access around for adhoc query and reporting, plus its integrating into sharepoint better and better. It might be time for me to bite the bullet and learn .Net, but I think I knew that.. Terry Mc > Excerpt for the integration of the new GUI, the bits for ADP2003 were > exactly the same as for ADP2000 and all of the known bugs were still [quoted text clipped - 18 lines] >> >> --alexT sylvain you're a f.cking lying bitch 2002 was vastly improved over 2000 >> My understanding at the moment is that future development >> of the ADP will be limited or non-existent > > Do you have any public source for this ? The best source for public information about Access 12 is Erik Rucker's (Group Program Manager for Access) blog at http://blogs.msdn.com/access/ However, the only real thing said there about ADPs is "The ADP architecture is conceptually unchanged between Access 2003 and Access12, which means that the features continue to work in essentially the same way they did. We continue to believe that SQL Server makes a great store for Access data and that building the UI either through linked tables or ADPs will continue to work well." SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) I guess what we have been looking for is a way to use Access as a front end for SQL Server. We have an old Access multiuser app with an MDB backend that has a hundred or so tables, that is nearing the end of its life. Performance is a big issue for us. This app is for analyitical lab results and has reports based on some very complicated queries bases on a dozen or more tables. Plus many sub-tables with there own multiple table queries. I though that using ADP would allow me to use SQL views, SP's, etc to speed this up considerably by puting the query processing load onto our SQL Server, and carry on using Access like I aways had. Plus I need to draw some data from a second SQL Server database. If you think that linked SQL Server tables with a Access front end could work as well or better I would feel better. Does this mean I would use ODBC and write passthrough Queries? If you can point me in the right direction I would appreciate it. With the exception of an A95 app I wrote years ago (that did some ODBC passthrough queries to an IBM AS400 database) I have been working with the standard split FE/BE mdb model. I liked the idea of being able to do everything from the ADP (create tables, view, SP) but that would not be the end of the world. At the end of the day I would like to create a frontend with the ease of Access and let SQL Server do the heavy lifting. Thanks > The Access MDB with linked tables is a superior tool to access SQL Server, > Oracle, DB2, etc. backends. The ADP was extremely limited in its s [quoted text clipped - 39 lines] >> >> Terry Mc If you still want to keep Access as the frontend while having SQL-Server as the backend and running complicated queries, then your only practical solution might be to use ADP. With MDB and Linked tables, the only ways of accelerating things are the use of Views and the cumbersome use of SQL passthrough queries. However, you will have fun to code forms based on pass-through queries under MDB. The support of ADP for SP is bad and full of bugs, but never as bad as the one offered by MDB. MDB with linked tables (and Views) is a good solution because it's simple to use for upsizing an already existing application, when there is no need to run complicated procedures on the SQL-Server side. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >I guess what we have been looking for is a way to use Access as a front end >for SQL Server. [quoted text clipped - 72 lines] >>> >>> Terry Mc Sorry Sylvain , that's a lot of misinformation that you have posted. As far back as Access 97 (and probably earlier), Jet has made every attempt to "pass through" every Access query against a linked ODBC datasource. There are limitations of course. For example, there is no way for SQL Server/DB2/Oracle/etc. to process your user defined functions or VBA functions that do not have SQL equivalents. There was only one case where I actually had to write a pass-through query to obtain better results and that was a query that deleted all the rows in a table. Take a look at the knowledge base articles on client/server optimization for Access. Also, take a look at the "Jet programmer's guide" for detail information regarding how Jet handles ODBC linked tables. The book is out of print but you may find a copy in your library or available from a used book seller. I got one for $5 from Amazon.Com last year. Most of my applications use Access front ends to a variety of ODBC back ends - DB2, Oracle, and SQL Server among others. In all cases, the applications use linked table and stored querydefs with parameters as the RecordSources for forms. The one thing you must avoid to make effective use of an ODBC back end is the common Access practice of basing forms directly on tables or on queries with no criteria. It is imperative that your forms be based on queries with selection criteria in order to limit the number of rows returned. The advantage of having an ODBC back end is that you can create triggers which will allow you to offload certain business rule processing that Access with Jet tables can only support via form events. I have never found it necessary to create stored procedures except in some complicated reporting situations. I would certainly never use a stored procedure or pass through query as the RecordSource for a form. They are not updatable and that would force you to use an unbound form. If you are going to go through the effort of using unbound forms, you might as well be writing in VB.Net or C++. Bound forms and reports are the biggest advantage of working in Access. Clearly a stored procedure would be faster than a query "passed through" by Jet whether the query was defined as a pass-through query or not. The difference is that the stored procedure is bound and the pass through query is not. Bound in this instance means that the query has been processed by the query analyzer and the best access path has been determined and stored for future use. This eliminates some overhead as a query starts. The situation is very much the same as the difference between using stored querydefs and using SQL strings to access Jet tables. In the case of the querydefs, Jet "binds" the query when it is saved and that eliminates work that SQL strings have to go through at run time - every time they are executed. If you use querydefs for your queries against linked ODBC tables rather than SQL strings, Jet processes the query when it is saved and "remembers" that it needs to pass it through and exactly how that needs to be done. So, even with ODBC tables, a stored querydef will be ever so slightly faster than an SQL string. > If you still want to keep Access as the frontend while having SQL-Server > as the backend and running complicated queries, then your only practical [quoted text clipped - 87 lines] >>>> >>>> Terry Mc I'm sorry, but I'm unable to see anything about some kind of misinformation that I would have posted from reading your answer. In my previous post, I have said that the support for SQL passthrough queries under MDB was bad and worst than the one offered by ADP while you have said, in your answer, that in the case of a MDB file these passthrough queries were read-only; which make mandatory the use of unbound forms in these cases. I'm sorry to say that but I don't see any tangible difference between these two opinions. I don't know if the queries needed by the original poster (OP) are really complicated and cannot be done otherwise or if they could be replaced with simple views or whatever else as I leave this analyse to the OP himself. Similarly, I have the same thinking about your work: you make your own decisions and if you don't need or don't want to use SP, then I'm glad for you. However and beside the fact that you don't use SP yourself, surely you don't expect me (and others) to limit myself and my work to your own knowledge/needs/decisions/line of work? SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Sorry Sylvain , that's a lot of misinformation that you have posted. > [quoted text clipped - 139 lines] >>>>> >>>>> Terry Mc The problem is that you are saying that an MDB with linked tables isn't a viable alternative to an ADP and I strongly disagree. "remains with Jet and linked tables or remains with Jet but go with SQL pass-through queries and unbound forms" It is most certainly not necessary to go with unbound forms or pass-through queries. Performance with bound forms is quite acceptable provided the RecordSource is a query with a where clause that limits the number of rows returned from the server and this has been the case as far back as A97. This is nothing new. > I'm sorry, but I'm unable to see anything about some kind of > misinformation that I would have posted from reading your answer. [quoted text clipped - 161 lines] >>>>>> >>>>>> Terry Mc I have to agree with Sylvain, here. There was no "misinformation" in his post, simply a different approach to doing things. There are certainly some very good arguments, at least in some instances, for using Stored Procedures and/or Functions to create recordsets instead of using Views themselves. I've even known some advocates of an SP-only approach, ignoring Views altogether. But more importantly, going back to the poster's original request, all I've seen about a lack of support for ADP's in future versions of Access has come from MVP's in newsgroups. Not one of you is actually on the Access development team, and so far, the best reference we have is a newsgroup blog, which neither confirms or denies the supposed lack of support for ADP's. If you're going to continue posting this "information", then perhaps you should post a source for it, lest it too be deemed "misinformation". Rob <snip> > There are certainly some > very good arguments, at least in some instances, for using Stored > Procedures > and/or Functions to create recordsets instead of using Views themselves. > I've even known some advocates of an SP-only approach, ignoring Views > altogether. <snip> If I may be forgiven for going off on something of a tangent here ... Given an environment that supports both views and stored procedures, just what *is* the purpose of views, anyhow? I ask because most of my work with SQL Server has been via ASP.NET rather than Access, and in that environment I've yet to find a use for a view. SignatureBrendan Reynolds > <snip> >> There are certainly some [quoted text clipped - 12 lines] > I ask because most of my work with SQL Server has been via ASP.NET rather > than Access, and in that environment I've yet to find a use for a view. Views allow you to join tables together, just like queries in Access. You can create views that denormalize your data for presentation purposes. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >> <snip> >>> There are certainly some [quoted text clipped - 15 lines] > Views allow you to join tables together, just like queries in Access. You > can create views that denormalize your data for presentation purposes. I know, Doug, but so do stored procedures. What I meant was, given an environment that supports both, what can you do with a view that you can *not* do with a stored procedure? SignatureBrendan Reynolds The first thing that comes to my mind would be to ramp up the security of confidential informations; particularly when you need a row level security not only for your users but also for your programmers. It's not all compagnies that are willing to accept that the programmer of a SP will have a full access to any confidential information inside a table and even for those that might accept this, the use of Views can reduce the risk of an "accidental" release or writing. The second thing is about performance: the use of indexed (materialized) views will boost performance and can only be achieved by using a view (you cannot simulate that inside a SP). Similarly, the use of partitioned views over a federated database will also boost performance in a way that cannot be really duplicated with a SP because of the lack of optimisation by SQL-Server in the later case. (See http://vyaskn.tripod.com/what_are_federated_databases.htm for more info on federated databases). Finally, even if you don't have security or performance concerns, the use of Views can simplify the writing of SP in the same way as the use of functions in general coding. This may be especially when you're not the only one working on a project. See http://odetocode.com/Articles/299.aspx for an example. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >>> <snip> >>>> There are certainly some [quoted text clipped - 21 lines] > environment that supports both, what can you do with a view that you can > *not* do with a stored procedure? Thanks Sylvain. That's interesting and useful information - I particularly like the idea of using views to simplify stored procedures. SignatureBrendan Reynolds > The first thing that comes to my mind would be to ramp up the security of > confidential informations; particularly when you need a row level security [quoted text clipped - 46 lines] >> environment that supports both, what can you do with a view that you can >> *not* do with a stored procedure? i use Views a LOT more than typical developers; they're basically the only way to get anything done using Analysis Services. This is the problem here: on one side, we don't have any clear information from MS about the future of ADP and all we have are very little pieces of information and some relative conclusions that can be drawn from the past. On the other side, the OP has made a request about a decision he has to take. Obviously, the best scenario would have be a full disclosure of information from MS but we don't have it and the fact that we don't have it doesn't discharge the OP from the responsability that he still have a decision to take. I don't know what will be his decision (to wait one more year (or more if his company have a policy of waiting until the release of SP2 so that the major bugs have been iron out) until Office 12 is out and then take his final decision; close his eyes and jump ow with ADP, whatever the real consequences will be; remains with Jet and linked tables or remains with Jet but go with SQL passthrough queries and unbound forms; take a look at .NET; etc.) and I don't care because this is his responsability only. All we can do is to provide him with the little pieces of information that we have at this moment and let him go making his own decision. Hopefully, he will take a good one but if he don't, then it's not our business. Your point about the requirement of posting a source is interesting but if you read my previous posts, you will see that this is exactly what I've always said: that we don't have any specific information about that subject from MS and that all we can do is to draw conclusions from other pieces of evidence. For those people who can take the luxury of waiting, these conclusions are probably useless but for those others who cannot afford this luxury, then this is only all we can give to them. These conclusions might be totally right or totally wrong, I don't know because I don't have ESP and cannot read the futur or in other minds; all I can say is that this is all we have at this moment and that the ultimate responsability of making a decision lays in the hand of the reader, not in my hands. My "information" is not information; they are only conclusions and they have always been labelled as such. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >I have to agree with Sylvain, here. There was no "misinformation" in his > post, simply a different approach to doing things. There are certainly [quoted text clipped - 17 lines] > > Rob This thread has been interesting. Clearly things are never black and white so I appreciate all points of view. From my point of view there are a number of certainties. First SQLServer 2005 is here and is much different than 2000 We have a purchased integrated business management package that runs on SQL Server 2000, and when this package can/needs to go to SQL Server 2005 we will. Our current LIMS (Lab Information Management System) is based on a very basic all Jet split FE/BE model and has served us well for 6 years (Ver 5) but is at the end of its life cycle. We have no plans to make new changes (other than bugs and bandaids) and we cannot wait until Office 12 (or SP2) to decide which way to go. We will be using SQL Server as the new backend. It was my understanding that in a FE/BE Access setup, the Jet in the front end did the query processing, the BE merely served up the data, and even if SQL tables where linked to Access FE, the jet FE still did the work. If I wanted something else for performance reasons then my options were either ADP (so I could easily work with SQL Server Views or SP's), or Pass-through queries. Otherwise I would be working with disconnected recordsets (or unbound forms) so I might as well go .Net. At the day, if the future of ADP is uncertain then it is unlikely I will go that way. But If I can achieve the same results with Access and a different method I would sure like to hear about it. Terry Mc > This is the problem here: on one side, we don't have any clear information > from MS about the future of ADP and all we have are very little pieces of [quoted text clipped - 53 lines] >> >> Rob Hi Terry, I'm sort of pulling a few messages in this thread together, and working on my own knowledge as well, but summarizing everything, here's how I see it: Access still lets SQL do most of the hard work in a FE MDB/BE SS2k setup, so that's probably the best way to go if the uncertain future of ADPs is a concern. It's not as easy to work with some things like Stored Procedures and Functions, and you can't do any serious back end design work from the front end like you could with ADPs, but if you limit yourself to the back end strictly being a provider of tables & views, and your front end providing most of the fancier functionality, then MDB with SS2k ODBC links will get the job done without placing an undue burden on your front end. That said, it's all too easy to make mistakes when designing an ODBC-based client/server database such that you end up pulling an entire table down to the front end to be processed, even though you only need a few rows. The two biggest "gotchas" that come to mind (and I believe have been mentioned elsewhere in the thread) are using custom functions in Access that SQL Server cannot duplicate, and joining a local table with a server-side table, directly or accidentally (typically through the use of multiple query levels, where you forget that your base queries are using local/server-side tables). Personally, I went with an ADP about a year ago, and haven't looked back since. But then again, at the time, I didn't have any indication that they might no longer be supported in future releases, either. :) For now, we've moved to an ADP and we'll stick with that unless/until I hear something absolutely definite from MS in regards to the future of ADPs. If I have to move back to an MDB-style design in the future, so be it. It means a few changes, but considering that the original design was a Jet FE/BE, it's probably not as big of a leap as it could have been. By then, we may have finished work on the VB middle tier, and may be wanting to migrate to .Net anyway...who knows? Best of luck, Rob If the app is ONLY designed for SQL Server7/2000, ADP is fine. However, If the app must work with SQL Server2005, you cannot do anything with Access ADP, and with Access12 almost there for the public and MS is still not willing to say something on this, I'd not bet my app on ADP. I remembered when SQL Server2K came out (after Access2000), there was almost right away a Access compatibility to SQL Server 2K patch available, aimed mostly for ADP to work with SQLServer2K/MSDE2K. Not this time when SQL Server2005 out, though. If you want to stick to MS and use SQL Server 2005, .NET might be the only option, at least MS wants you so , for now. > Hi Terry, > [quoted text clipped - 34 lines] > Best of luck, > Rob Norman you are full of sh.t; microsoft is coming out with a patch for SQL 2005 and ADP 2003. the fact that we were required to have a patch when Access 2000 against SQL 2000 is an indication that MS is mismanaging Microsoft Access. I call for the public execution of all MS management in charge of Access. it is obvious that they aren't doing their jobs correctly. get off your fat a.s, microsoft; and start fixing bugs. You're right that with linked tables, the Jet FE will do most of the work but this is true only if you don't use Views. With views (and taking some precautions about VBA functions), must of the work can be done on the SQL-Server. If you don't use views, your application might be even slower than with using Jet as the BE. This good news has its cost: first, you must evidently write a whole bunch of views and use them everywhere. This process is not so much different than creating SP and will requires a lot of work in the same way as creating a bunch of SP; however, their overall capabilities are much more limited, not only for selecting records but also for saving (writing) through them. So this will be good as long as their use is sufficient to covers your needs but as soon as you will need a little more from your application, then it's overall performance will drop. However and whatever your final choice, upsizing your MDB application to a MDB file with linked tables to SQL-Server might be seen as a good introduction to SQL-Server and nothing forbid to take a look at ADP and/or .NET later while still keeping the MDB file as an intermediary step. For updatable linked views: http://support.microsoft.com/kb/q209123/ SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > This thread has been interesting. > [quoted text clipped - 91 lines] >>> >>> Rob "You're right that with linked tables, the Jet FE will do most of the work but this is true only if you don't use Views." That's not what Robert said. He said that the server will do most of the work. As I said earlier, if you understand the constraints - ALL Jet queries will be passed through to the server for processing. Information regarding how jet works is available in the "Jet Programmer's Guide". It is out of print but you can probably find a used copy on Amazon or ebay. I was at a meeting with the A12 design team in Redmond in March and made a point of discussing how Jet behaves with linked ODBC tables and was reassured by the Jet team lead that Jet always attempts to pass-through the query. It only does local processing as a last resort. I have used Access MDB FE's to Oracle, DB2, Sybase, and SQL Server BE's and never had response issues even with large (hundreds of thousands of rows) tables. MSFT isn't planning on removing support for existing ADP's but they are not enhancing their current functionality. I believe that in the blog that was referenced earlier in the thread, they mentioned that there would be no design support for SQL Server added to the current Access release and it was not being provided for A12 which is planned for the end of 2006. Access ADPs will be able to link to 2005 databases. They just won't be able to make any design changes. You'll need to use Enterprise manager. Sylvain, I really didn't mean to cause a problem. You obviously know a great deal about ADPs but they are not the only solution as you keep implying. The Access team has also come to that conclusion. Rather than dividing their efforts by trying to maintain two very different environments, they are cutting their losses and moving on. They made the same decision regarding Data Access Pages. > You're right that with linked tables, the Jet FE will do most of the work > but this is true only if you don't use Views. With views (and taking some [quoted text clipped - 118 lines] >>>> >>>> Rob Well, in case like this, you open the SQL-Server Profiler and you take a look at what happens with a quick test. So I made a query based on three tables: Organismes, Ligues and Equipes; simple joins, no Where, all fields retrieved. It appears that you are partially right: the JET use a Select JOIN query but only for retrieving the ID of the three tables but after that, it use separates queries to retrieve the other fields by group of ten records: 10 records for the table Organismes, then 10 record for the table Ligues and then finally 10 records for Equipes, etc.; repeating as necessary to get all the records that it needs. Of course, the above example is far from reality because it's based on only three tables. With the real schema, the table Ligues is linked to eleven other tables and its formulaires has about 12 subforms and many combobox and many of these subforms and comboboxes are themselves based on Select queries joining multiples tables. Needless to say that with the method JET is using to retrieve the fields separately for each table by group of ten records, this will require a fast LAN with a lot of availability and will generate a lot of circulation on the network. Over the WAN, it's probably totally useless. Even if I don't use JET linked tables, I hope that the Access team will take the time of changing this ridiculous behavior in the next version of Access and reduce this great number of unnecessary round-trips to the server for even simple queries. In its actual state, this is a strangulation of performance and explains why everyone else is suggesting to use Views everywhere when working with linked tables on a SQL-Server backend. Finally, I never said that using a MDB FE with linked tables was a bad solution; in fact, I've made the suggestion of using this many times in the past. The only thing that I'm saying about this solution is that it can only be seen as a first step because it only gives the possibility of taking a fraction of the power of SQL-Server. If this is enough for you, than search no more but if you need something more, then you have to go with another solution than the use of linked tables and/or linked views. Finally, about the futur of ADP, if you read some of my previous in this newsgroup, you will see that's now more than two years that I've said that ADP were doomed because MS have took the decision to let them go and replace them with .NET technologies. MS never make any public announcement about this but this conclusion was pretty obvious not only to me but also to a bunch of other people when Access 2003 came out: excerpt for the color of the menus, it was strictly identical to ADP 2002; with exactly the same features, the same limitations and none of the numerous known bugs had been corrected and the subsequent releases of SP for Office made it clear that this conclusion was true. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > "You're right that with linked tables, the Jet FE will do most of the work > but this is true only if you don't use Views." [quoted text clipped - 154 lines] >>>>> >>>>> Rob Hum, Technet at Microsoft should clears up their things; look under the section "Using Views for Queries with Complex Joins" in: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Well, in case like this, you open the SQL-Server Profiler and you take a > look at what happens with a quick test. So I made a query based on three [quoted text clipped - 204 lines] >>>>>> >>>>>> Rob Try your query with ALL the tables but only select a limited set of records rather than the entire table. The reason that Jet is bringing back only a few records at a time is because there is a limited number of records you can view on a form and it wants to be able to populate the form's recordset as quickly as possible so the form will open up without delay. If it retrieved all the records before opening the form, performance would be dismal indeed. This is why is simply makes no sense to use unqualified queries as RecordSources for forms. The user isn't going to look at thousands of records. Why drag them to the client? > Well, in case like this, you open the SQL-Server Profiler and you take a > look at what happens with a quick test. So I made a query based on three [quoted text clipped - 204 lines] >>>>>> >>>>>> Rob Of course I have made a test with a limited number or records. However, as JET doesn't have any clue on the real number of records in the SQL-Server tables, this change nothing as expected. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Try your query with ALL the tables but only select a limited set of > records rather than the entire table. The reason that Jet is bringing [quoted text clipped - 216 lines] >>>>>>> >>>>>>> Rob sylvain this sh.t Even if I don't use JET linked tables, I hope that the Access team will take the time of changing this ridiculous behavior in the next version of Access and reduce this great number of unnecessary round-trips to the server for even simple queries. In its actual state, this is a strangulation of performance and explains why everyone else is suggesting to use Views everywhere when working with linked tables on a SQL-Server backend is why MDB is a disease. USE ADP AND DONT LISTEN TO THESE MDB-SISSIES!!! is Sylvain a guy? i thought it was a chick; i mean.. only an emotional bitch would put out misinformation like this. you can use ADP in 2002 or 2003 and you have an AWESOME sproc designer. MUCH MUCH MUCH better than in 2000; better than in Visual Studio; or Enterprise Manager. you're crazy you can update a form that uses a sproc under certain circumstances; i swear and they work a lot better for little lookup queries and all that terry use ADP don't listen to these a.sholes pat you're a f.cking idiot and a crackhead mdb is a joke not reliable not stable and not dependable. it's not faster. it's not more flexible. MDB is crap. They are coming out with a ADP SQL 2005 patch soon. Terry, I've found this thread to be quite interesting and just thought I'd offer my humble opinion. I am a big fan of the ADP/SQL server combination. I really fought it at first, as it does require a different coding style than MDB solutions, probably the five biggest differences that I've noticed are 1)Not using VBA functions in Queries 2)Figuring out how to pass parameters to stored procedures 3)The preference to use ADO as apposed to DAO and 4)Lack of local data storage 5)Knowing when to use SQL's ' and when to use VBA's " (this one can really make you pull your hair out!) With that said, once I learned my way around using ADP/SQL server, I have never looked back. For example, for simple form filters, in ADP, MS Access will automatically setup a server filter. This allows the SQL server to only return the request row. Another advantage is that there is no question where queries are being evaluated, you do not have to worry about accidentally bringing large tables and queries to the workstation. It took me a while to figure out how to pass parameters to a stored procedure programmatically, as it didn't seem to be documented well, but once I figured that out, I've found stored procedures to be my best friend. One of the advantages that I've found to using ADP is the "implied" connection that always exists. To me, it's a very powerful feature to just type docmd.runsql "sql statement...." and have any sql statement execute against my SQL server, for example, this syntax could be used with the xp_shellcmd to execute a local command on the SQL server or xp_sendmail to have the SQL server send an email (which can be a lot easier than dealing with MAPI at the workstation in some cases) If/when your line of business app migrates to SQL server 2005, I wouldn't think it would be a big deal to continue to run SQL 2K on a different box, as you'll probably want to upgrade your hardware when you migrate to SQL server 2005 anyway. I personally will be sticking to ADP files as long as they are available, however, I will be changing to .NET if they ever take my beloved ADP files away. Just sharing my opinion here of course. I'm a believer in ADP, but I do not feel the need to be as rude about it as some people. Mark Shultz Procurement Data Specialist RAD, Inc. > I just started reading this group and disapointed to read that it sounds > like ADPs are being hung out to dry. [quoted text clipped - 16 lines] > > Terry Mc I just wanted to add a couple things to my previous post, My motivation for using ADP files is more for the end-user performance, not for the SQL design tools. ADP files utilize a completely different model for accessing data than jet and thus has much faster experience for the end user...that is, after all, who most of us are developing for. My users like being able to query a table with nearly 6 million records in a few seconds. There are some query types that the ADP query designer doesn't support, such as the insert from type. With that said, the query designer that is part of the Enterprise manager does support building these types of queries and the SQL can simply be pasted into the SP designer in ms access, or you can create the SP directly from enterprise manager. I like having the flexibility to do both. Another HUGE advantage to using ADP/SQL is there is no ODBC links to maintain at the client. You simply make sure the client has a current version of the front end and that's where the client setup ends. Even when dealing with other data sources outside the SQL server, you can use the "Linked Server" functions on the SQL server, this way you only have ONE connection to maintain to your other data sources, as opposed to a connection from every workstation. This also means that any joining of data with other data sources, such as data from your line of business app, which you pointed out also uses a SQL Server DB is evaluated on the server. And in a case like this, where both apps are on the same server, you can access data from your line of business app using simple 3 part tables names, and data from other outside sources, even data from access mdb's using 4 part table names. ok, end of rant...lol I hope everyone is having a good Friday! Mark Shultz Procurement Data Specialist RAD, Inc. > Terry, > [quoted text clipped - 70 lines] > > > > Terry Mc mark i totally agree with you im sorry that im come across as rude every mdb-sissie in the world sits around and talks trash about ADP; just because they're too scared to learn a little bit about SQL Server. and I think that it's ridiculous. ADP is the most important app to come out of redmond; ever-- and Redmond just doesn't take it seriously. it just drives me crazy. I got fired from Microsoft TWICE for screaming bloody murder about all these goddamn bugs open up QA a) create proc sphappy as select * from sysobjects b) open the proc in adp; change the sql statement; his save you get the error 'the parameter is incorrect' I believe that this is representative that MS isn't taking ADP seriously enough I mean.. get real MS; ADP chokes on the 'create proc' syntax? (vs create procedure) that syntax is listed in books online and it just pisses me off that you fat lazy microsoft dumbasses won't fix this bug. i mean-- you guys can eat sh.t; i would rather work for free against mySql than continue to sing the praises of your piece of crap architecture. Microsoft just needs to raise the bar start taking this sh.t seriously. AND MARKET ACCESS. When was the last time you saw a commercial about MS access?? why do we have commercials about the xbox but not about ADP? eat sh.t microsoft f.cking piece of sh.t company; crack-smoking foreigners OH LETS GET DRUNK AT LUNCH AND DRIVE AROUND MY BMW wake up you fat, lazy company |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.