none
Access 2013 drops support for adp projects. What alternatives do you suggest?

    Question

  • My main application (Screenshot of a typically complex form attached):

    MS Access 2003-2010 adp Project containing 100+ forms including modal dialogs and main forms with header, footer and up to 3 subforms, each looking at their own linked data, 64 reports/subreports and 34 modules including MS Office and Windows API integrations. 

    This looks at an SQL 2005-2012 database with 52 Tables, 64 views, 44 stored procs and 38 functions

    So what are the viable alternatives to Access 2010 .ADP -> SQL Server 2012?

    Requirements:

    Rich forms/subforms environment with vb method coding, event handling, report generation, binding to fully relational SQL database with table, view and stored proc objects:

    • Microsoft Lightswitch (Desktop or Web App)

    Quick and lean but probably too limited for serious development

    • Sharepoint Designer Lists etc using BDC

    Simple for single table interaction, don’t know what potential

    • Sharepoint Visual Web Parts via Visual Studio

    Little support for RAD or database bound controls

    • Access 2013 accb with SQL Server linked tables

    Path of least resistance. Don’t imagine this supports stored procs etc

    • Access 2013 Sharepoint Web App

    MS vision for the future Don’t know much about this yet. Requires specific client environment restricting market but may be the way others want to go.

    • Visual Studio Web App/Windows Forms with only .net Data Controls

    Cheap option but hard work. Complex forms with data linked controls are tedious to develop without 3<sup>rd</sup> party tools. No bundled reporting option eg Crystal

    • Visual Studio Web App/Windows Forms with 3rd Party dev tools:

    Much easier development but can be expensive and may result in continuing commitment and high dependency on the future/support of the 3rd party. Which of these tools to consider?

    What would you do? Or are you already on this road. How are you finding it?

    Grateful for any suggestions!

    Stephen Solt 

    Construction industry database developer

    Planchest ltd

    Warmington PETERBOROUGH UK

    • Edited by s_solt Tuesday, November 13, 2012 12:09 PM
    Tuesday, November 13, 2012 12:04 PM

Answers

  • >Access 2013 accb with SQL Server linked tables
    >Path of least resistance. Don’t imagine this supports stored procs etc

    I'm not sure where your information for the above conclusion comes from, but it is completely incorrect. 

    The vast majority of the access developers in the Access community for a good 10+ years now have STRONGLY recommended that linked tables are a better choice than ADP. We find linked tables have better flexibility, allows local tables, and simply in general results in more choices as opposed to using an ADP project. This widespread knowledge in our industry is certainly not new.

    >Path of least resistance. Don’t imagine this supports stored procs etc
    >Access 2013 Sharepoint Web App

    Actually, the 2013 Access web setup now uses native SQL server tables for the back end data store. In theory this should allow one to use triggers that call store procedures. However, I don't think Access Web will give you the rich UI for a web based front end. And you don't have a practical web interface to calling and pass information back and forth to those store procedures of which one now can in theory now write with SQL Azure. So at this point in time, I don't recommend the current front end web development system for rich "custom" user interfaces.

    However, you could most certainly consider using SharePoint as your back end, and using a classic VBA Access application as your front end with linked tables. This option would much depend on how much data processing the application does now and how it works. Such a setup does allow "off line" mode which means laptops (or the new "pro" tablets) could run without a connection to the server until such time you "sync". And I dare say, that such "off line" applications that are more dependent on DAO code will work MUCH better then ADO. In fact with this setup, I would suggest you dump use of the ADO object model in favor of DAO if you were going to consider this road. ADO not really happy with SharePoint at all.

    The best suggestion here?

    The tried and true and LONG standing standard suggestion in all of the Access developer circles and communities that I can think of that have existed for the last 10 years here is to simply use linked tables to SQL server.

    A linked table setup allows you to continue to utilize and call and pass parameters to store SQL procedures on SQL server.

    A linked table setup allows you to continue to use your ADO VBA recordset processing code. The fact that you dump an ADP Project and adopt linked table to SQL does not prevent use of your VBA recordsset code, nor as noted stop you from using store procedures on SQL server.

    You will certainly have to change a few of your developer habits and processes. For example Access developers often adopt the use SQL server "views" and link to these views from the Access front end. This approach is done for things like reports as you can then simply pass a "where" clause to the open report command to achieve filtering of the report. This means you don't mess around with query parameters nor have to add them to the sql or the SP. (and you get good performance).


    Do keep in mind that the create/edit/maintains of views and store procedures will NOW have to occur by using the SQL server management tools (SQL management studio or what is often called SSMS).

    However the query designer and even the store procedure editor in SSMS are going to take one about 10 minutes to learn if you used SQL server all these years with Access anyway.

    So, in general if you adopt and keep a standard Access front end, you simply drop the ADP project and now use linked tables you are still in not too bad shape.

    It's is rather unfortunate you started this project as an ADP, since at the end of the day, using ADP's over standard linked tables gave you few advantages.

    For those who adopted linked tables from day 1 are thus now not here asking any questions about what their options are since in regard to linked tables nothing needs to be changed for 2013!

    Depending on your developer habits, and how you approached many of your solutions, then adopting a standard Access front end with linked tables to SQL server should certainly be by far be the LEAST AMOUNT of work here. This setup should allow you to preserve most if not all of your store procedure code, and in fact most of your Access client side code. And the learning curve will be relative short, and most of your form coding practices etc. should conitnue to work with only a few changes in approach.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, November 15, 2012 6:33 AM
  • Hi Stephen,

    mere ODBC linked tables. This path is ultimately close to standard Access back-end development process. As for myself, I've never used ADPs, I always use linked tables w/o any problems.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Tuesday, November 13, 2012 12:22 PM

All replies

  • Hi Stephen,

    mere ODBC linked tables. This path is ultimately close to standard Access back-end development process. As for myself, I've never used ADPs, I always use linked tables w/o any problems.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Tuesday, November 13, 2012 12:22 PM
  • >Access 2013 accb with SQL Server linked tables
    >Path of least resistance. Don’t imagine this supports stored procs etc

    I'm not sure where your information for the above conclusion comes from, but it is completely incorrect. 

    The vast majority of the access developers in the Access community for a good 10+ years now have STRONGLY recommended that linked tables are a better choice than ADP. We find linked tables have better flexibility, allows local tables, and simply in general results in more choices as opposed to using an ADP project. This widespread knowledge in our industry is certainly not new.

    >Path of least resistance. Don’t imagine this supports stored procs etc
    >Access 2013 Sharepoint Web App

    Actually, the 2013 Access web setup now uses native SQL server tables for the back end data store. In theory this should allow one to use triggers that call store procedures. However, I don't think Access Web will give you the rich UI for a web based front end. And you don't have a practical web interface to calling and pass information back and forth to those store procedures of which one now can in theory now write with SQL Azure. So at this point in time, I don't recommend the current front end web development system for rich "custom" user interfaces.

    However, you could most certainly consider using SharePoint as your back end, and using a classic VBA Access application as your front end with linked tables. This option would much depend on how much data processing the application does now and how it works. Such a setup does allow "off line" mode which means laptops (or the new "pro" tablets) could run without a connection to the server until such time you "sync". And I dare say, that such "off line" applications that are more dependent on DAO code will work MUCH better then ADO. In fact with this setup, I would suggest you dump use of the ADO object model in favor of DAO if you were going to consider this road. ADO not really happy with SharePoint at all.

    The best suggestion here?

    The tried and true and LONG standing standard suggestion in all of the Access developer circles and communities that I can think of that have existed for the last 10 years here is to simply use linked tables to SQL server.

    A linked table setup allows you to continue to utilize and call and pass parameters to store SQL procedures on SQL server.

    A linked table setup allows you to continue to use your ADO VBA recordset processing code. The fact that you dump an ADP Project and adopt linked table to SQL does not prevent use of your VBA recordsset code, nor as noted stop you from using store procedures on SQL server.

    You will certainly have to change a few of your developer habits and processes. For example Access developers often adopt the use SQL server "views" and link to these views from the Access front end. This approach is done for things like reports as you can then simply pass a "where" clause to the open report command to achieve filtering of the report. This means you don't mess around with query parameters nor have to add them to the sql or the SP. (and you get good performance).


    Do keep in mind that the create/edit/maintains of views and store procedures will NOW have to occur by using the SQL server management tools (SQL management studio or what is often called SSMS).

    However the query designer and even the store procedure editor in SSMS are going to take one about 10 minutes to learn if you used SQL server all these years with Access anyway.

    So, in general if you adopt and keep a standard Access front end, you simply drop the ADP project and now use linked tables you are still in not too bad shape.

    It's is rather unfortunate you started this project as an ADP, since at the end of the day, using ADP's over standard linked tables gave you few advantages.

    For those who adopted linked tables from day 1 are thus now not here asking any questions about what their options are since in regard to linked tables nothing needs to be changed for 2013!

    Depending on your developer habits, and how you approached many of your solutions, then adopting a standard Access front end with linked tables to SQL server should certainly be by far be the LEAST AMOUNT of work here. This setup should allow you to preserve most if not all of your store procedure code, and in fact most of your Access client side code. And the learning curve will be relative short, and most of your form coding practices etc. should conitnue to work with only a few changes in approach.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, November 15, 2012 6:33 AM
  • Hi, Albert.

    Our application uses an MS Access 2007 adp Project but it is way bigger and far more complex than Sthephen's one. It contains 700+ forms, modal dialogs, main forms with header, footer and up to 3 subforms, each looking at their own linked data, hundreds of reports/subreports. This looks at an SQL 2005-2008 database with 347 Tables, tons of views, many stored procs and functions.

    Do you still think that linked tables is the way to go?

    Thanks in advance.

    Enrico Berengan
    ByteWare s.r.l.
    Spoleto, Italy

    Tuesday, November 27, 2012 9:58 PM
  • I have been using Access 2003 with ODBC-linked Tables with 300+ Tables from SQL Sever 2005 BE without major problems to date.  Since Access can handle approx. 32K objects (linked Tables, Queries, Forms, Reports, ...), I think we still have a long way to reach this limit.


    Van Dinh

    Tuesday, November 27, 2012 10:14 PM
  • First, yes, I most certainly recommend the linked table approach. This approach will save (salvage?) most of your code. You can create a new database and import the forms + code you have now. There will be some re-working but "most" of the basic application will be preserved.

    The above seems like a far more sensible and cost effective approach than a complete rewrite of the system in another language or platform (which no doubt would incur significant expense).

    Your challenge is the size of your application. In other words a small amount of changes to some forms could STILL result signification amounts of work due to the sheer application size. 

    In a way this is kind of like cutting grass. A small little manual push mower works for a great little flat square piece of grass on your boulevard that hardly would allow a gas powered lawn mower to warm up.

    For larger yards than a gas powered lawn mower is required.

    And for larger yet you need a riding mower.

    So in all cases were are just cutting some grass here.  The caution and difficulty in your scenario is the sheer size of the application. Therefore the scale of changes can be quite large despite me telling you that changes from ADP to linked tables are relative minor in nature say for each form.

    So you want to do some "pilot" tests as to how long and how much work it takes to convert say ONE particular form to operate correctly with linked tables. With this metric then you have an idea of the cost and time of such project.  So just like the lawnmower scenario, are you walking 1 mile, or 100 miles? 

    If the access developers are familiar and have worked with SQL server, and especially that of converting applications to linked tables, then like all things in life, more experience should result in less effort and time for such a conversion.

     In other words much of the challenge here will be the familiarity of the developers and changing the way in which they work. Some code and solutions built around doing things the "ADP" way will have to be changed.  In other words you don't want to remain focused on how come something works different, but ensure one is flexible in which one is willing to change how things were done compared to the past. This "lack" of flexibility is often the greater challenge then the technical ones!

    So for a form with SQL parameters you thus change this form to using the where clause of the open form command. Not a big change, but as noted small changes in a large number forms can still add up to significant work.

    On the other hand before a changeover to linked tables, have you asked yourself do you really need to do this?

    In other words, what is stopping you from using access 2010 for the next 10 years?  Or even better would be to use the free runtime edition of Access 2010?

    I mean for the most part, why is it a problem if you use an older version of access to run this application?

    In fact what is stopping you from using Access 2000 from 12 years ago right now? (really?)

    Right now, on a brand new windows 7 box (64 bit edition of windows), you can install + run Access 97 (and I think access 95). And I am NOT talking about using XP mode, but simply installing the software.

    So we are now talking about running a 15 year old program on a brand new box!

    So you could take the free edition of the Access 2010 runtime and I would think run it easy for another 10 years.

    So this would suggest that the ADP's lifespan would now have been OVER 20 years.

    So compared to products like turbo Pascal, Knowledge Man, Paradox, Reflex, FoxPro, dbase III, and a pretty significant long list? Most of these products were lucky to go for 10 years let alone 20.

    And in fact since ADP's came out in 2000, then we are now about 12-13 years later and again this is a significant timeframe in the computer industry.  So I see little reasons why you can't use a runtime edition of access for another 10 years.

    This would then suggest that over the next 10 years you have ample time to convert the ADP to linked tables or not even do anything at all.

    So you only need to convert to linked tables if your goal is to upgrade and use the latest edition of access. Realistically your only choice here is to adopt and convert the application to using linked tables if this is your goal.

    However once you convert linked tables, what have you gained here?

    In other words, it probably makes the most sense to continue to use the product with the access runtime for the next 10 years.  However if the product is to continue to be enhanced, and you want to use some of the new web features, or now the new ability to use office 365 and SQL Azure as the backend database, then I think it would be worthwhile to consider upgrading to the newest version of access.

    So the real choice centers around if you want to spend the money to convert to link tables and use the product for the next 10 years, or keep the product as an ADP, and simply deploy a runtime edition of the product for the next 10 years.

    I suppose much of the above will depend on what the future plans and direction are of which you would like to take this product in the future. 

    Keep in mind that about every 9 to 10 years the computer industry will have undergone a significant if not spectacular change. In 1990 we were still in a "text based" or what is often called the green screen type of user interface. So we are talking about DOS based products such as dBase or FoxPro. 10 years later in 2000 virtually NOBODY was using text based interfaces, and everything was mouse + graphical or windows based.

    Now another 10 years later? It is the web and the cloud. And tablets and smartphones are all the rage. And the new version of Access 2013 has some nice additions for touched base devices and also now has some web publishing ability.

    So Access is exceptional in how long it been around, and even more amazing is the newer version has cloud and web options. So you can now use the cloud edition of SQL (SQL running on Azure) for example.

    So all over these 10 year periods, some pretty significant changes occur in our industry, and this will continue to occur for the next 10 years. 

    Considering that I can install a 1990 version of FoxPro on a brand new windows box (22 years old), then given the track record of being able to install and run older legacy software on windows boxes is actually VERY good.

    So a timeframe of 10 years does show that backwards compatibility of running older software tends to be a hallmark of Microsoft and the windows desktop system. 

    I think based on this past history, it's likely reasonable to assume that you can run the existing version of office for at least the next 10 years.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, November 28, 2012 5:50 AM
  • Hi, Albert. Thanks for answering...Really helpful.

    Our main concern is all about compatibilty with SQL Server 2012 and its future releases.

    Is Access 2007 ADP fully compatible with SQL Srv 2012?

    If not, should we move to Access 2010 ADP to achieve maximum compatibility? What are the pros of using Acces 2010 ADP compared to Access 2007?

    Thanks a lot.

    Enrico Berengan
    ByteWare s.r.l.
    Spoleto, Italy

     

    Thursday, November 29, 2012 12:18 PM
  • For us the discontinuation of ADP is catastrophic.

    We have also complex AccessProjects. The biggest one has 200+ tables, 330+ functions, 140+ Views, 250+ StoredProcs and 550+ Formulas. In addition several hundredthousend SQLstatements in the VBAcode. Tha project has a SQL database with many mill records.

    Of course we shoud have programmed under .NET, but in 2003 it was not actual topic at that time.

    It will cost .5 mill dollars to convert/rewrite into eather .NET or ACCESS/ODBC. Not realistic

    What kind of market analyze hae been done.

    I see many new features in Access 2013, but those are for cosmetic design of forms, not in the power of manipulating big databases (SQL). You dont reach the power of SQL throug ODBC compared to what is possible in ADP!

    Trond Reitan

    trond@heimdaldata.as

    Wednesday, December 12, 2012 3:12 PM
  • I should keep up with the industry more.

    I have just started an Access 97 mdb to Access 2010 adp conversion. I thought that the main advantage of using an adp was that all of the querying and processing could be done on the server, whereas with an mdb and linked SQL tables, all data must be pulled over the network before queries and filters are applied, which is inefficient.

    Am I missing something here? My client likes to pull fairly large recordsets, and apply filters to the forms to drill down to see the records they want.

    Albert, in past years I have found your posts to be extremely helpful and this one looks like it may potentially save me a lot of rework.

    Dave Humphreys
    Vancouver, BC Canada

    Thursday, December 13, 2012 5:54 PM
  • I have just started an Access 97 mdb to Access 2010 adp conversion. I thought that the main advantage of using an adp was that all of the querying and processing could be done on the server, whereas with an mdb and linked SQL tables, all data must be pulled over the network before queries and filters are applied, which is inefficient.

    Dave, you are extremely wrong here. Just run SQL Server Profiler and check what is actually sent over the network. The only case when there is more data over the network than you need, is when you use VBA functions or some other built-in Access functions, for example, in queries which can't be translated to T-SQL. In other words, you get what you ask.

    So the only inefficient way here is to rewrite .mdb to .adp. No offense, it is a really profitless idea. 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, December 13, 2012 6:27 PM
  • Hopefully you can about face. While not ideal, it sounds like you just starting this.

    The real difficult part is for those with large investments in ADP's already.

    In your case to use SQL server then linked tables is now the way to go. And the "bonus" is any DAO code you have can REMAIN in use. If you go ADP, then you MUST dump and drop any VBA DAO code (so this is one big downside of ADP).

    With Open Database option (odbc), then you can continue to use your VBA code and that includes DAO code.

    As for lots of filtering etc? this should not be a problem or issue. If you going to filter a query that has several complex joins or especially a query with aggregates? Then create that same query as a view SQL server side. You then link to the view using the linked table manager.  Now the joins, and aggregates occur server side.

    The result is Access filters on that view will work very well indeed (and not pull down all records).

    So don't shy away from filtering etc. here. So even complex filtering should and can work fine with linked tables. Linked tables and SQL server is a NICE setup in Access.

    Just keep in mind that you may have to modify your approach and say perhaps use a link to a view as opposed to a table as suggested above in some cases to obtain great performance. After a bit of experience, you quite well see/know when such types of approaches are required.

    Good luck.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

     

    Friday, December 14, 2012 6:28 AM
  • Access 2013 has new features that allow it to use SQL Azure. So there are features in the new product that are for support of SQL server.

    As for your application?

    That sounds like a impressive application. And as you well note, it represents a sizeable investment.

    It would be hindsight to suggest that if you had used ODBC, then you would not be posting here!

    However, as noted, there's nothing stopping you from installing access 2000 on a brand new computer, and that includes even a windows 8 box. (and yes, I do mean 2000 – not a type-o).

    And say from today for say 10 more years one could adopt 2010 runtime. That would thus suggest a close to 20 year lifespan for that piece of software.  I think that's a remarkable time frame.

    When I think of all the applications written in FoxPro, Knowledge man, Paradox, Turbo Pascal, Dbase etc.? They all had a GREAT run in our industry. In fact Access ADP' have ALREADY had a LONGER life span then those past examples! Now this news doesn't sugarcoat or help your situation.

    However, I am just pointing out that the number of years you been able to run ADP's has been quite good. And in theory with the adoption of the a2010 runtime I don't see why you cannot continue using that ADP system for say another 10 years. So at that point you would be WELL beyond the lifespan of the given technology anyway.

    As I pointed out, in the Computer Technology industry, the whole industry goes through a relatively major change about every 10 years.

    >You dont reach the power of SQL throug ODBC compared to what is possible in ADP!

    I respectfully disagree with the above.  There's inherently not that many advantages to using an ADP' project.  In other words I admit there are some advantages,. However there are also a number of disadvantages.  With odbc you can utilize different servers, and also use local tables. In some cases, the ability to have local tables is significant advantages over ADP.

    I fully accept in your case you can (and did) make a case that you saw more advantages than downsides by using an ADP over that Open Database connectivity. 

    However in terms of scalability and performance it is much a myth that ADP's will produce better performance.  With experience in either platform (Open Database or ADP), then one should not see any difference in scalability or performance in either case. 

    In other words it only those with a hammer who can only see a nail as the target.  The choice of ADP over Open Database Connectivity is not a slam dunk.

    As noted, comparing these systems in terms of performance, scalability, and flexibility?

    For the most part open database connectivity when used with Access will yield the same performance and scalability as ADP's projects.

    And at the end of the day, as I stated, there nothing stopping you from installing Access 2000 on a brand new windows box. So nothing from the Access side is forcing you to upgrade to Access 2003 for example. (why did one bother to upgrade to 2003 for example? Nothing forced you?). (and this is not a type-o, I do mean 2000 and 2003 – not 2010 and 2013!)

    In fact, as pointed out elsewhere in this thread, the future problem here will not be you running the 13 year old version of Access 2000 on a brand new windows 8 computer. In fact since Access 2000 installs + runs on a brand new box, then Access is not the problem anymore – is it?

    Your problem is that SQL server and the fact that oleDB support is being dropped is what will really bite here.

    While the above may not be a great consolation, this does suggest that you likely have about 10 years here to convert over to some other technology. 

    This is not like the lights are being turned out here in one night. So you have time here.

    And dare I say the part about ODBC called "open" is a true statement. So migration to odbc is still a possible road.  However, while migration to linked tables  would STILL permit you to use ADO code,  I would recommend that such code be changed to DAO.

    In other words, no matter what other technology you adopt, if you going to adopt some type of server and client there is a good chance you going to be using Open Database connectivity for your new system unless you jump to 100% web – and even then you may well be using odbc.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canad


    Friday, December 14, 2012 6:35 AM
  • Thanks for the above article. I have an adp project using Ms Access 2000 and SQL 2000 server. Have install MS Access runtime to Various Machines win 8 and 7 and my project still works. I stayed with the project as i did not want to learn new "things" and did not have the time to rewrite code to later version of MS Access. Today I install Access 2013 (because I need to upgrade all my companies Hardware servers included as I still run SBS 2000.) and found out about the above problem. 

    These reply's have helped. I have decided to use my apd project on new server but will start to write/convert my project to linked tables in sections e.g. POS, Stock, payroll

    I went over to adp project for the benift of speed but from the above articles I see that is not necessarily the best option.

    Thanks for you info

    Bernhard Wolf (FIGJAM)

    Port Elizabeth, South Africa


    Thursday, January 10, 2013 7:41 PM
  • With the application I am currently working on, the client is insistent that all 5000 records be loaded at once so taht they can put their own filters sorts etc on the dataset at any time. That's okay because 5000 really isn't that much. The interface also has two listboxes which are populated by fairly complex queries that reference the main forms primary key. Performance is good; you can page through records very quickly.

    I was initially going to rework this program as an adp, with a stored procedure for the main form and stored procedures for the list boxes. On the current event for each record, the row source for the list boxes was to be reassigned to the stored procedure name with the current primary key passed as a parameter. This also was quite snappy, though maybe 10% slower than the original linked table application. It really didn't make a big difference.

    Now, I have gone back to the original linked table architecture, with a native query as the form's recordsource. However, I thought that, since it is read-only data, I could use pass-through queries for the list boxes' rowsource, and add a WHERE  clause on each current record to filter to the primary key. This is deathly slow - one third of the original performance. I found that very surprising - I thought pass-through queries would really speed performance if there were a lot of joins! It's the WHERE clause that's causing the problem, even though the criterion field is indexed.

    I think the next thing I will try will be linking to a SQL view, but is it really just the best idea to do an Access query with a [Forms]![MainForm]![PrimaryKey] criterion on one of the fields? What is the "Best Practice" in this case? I am using Pass-through queries to populate all combo boxes that don't have interface-related criteria attached - is that recommended?

    Dave Humphreys
    Vancouver, BC Canada

    Friday, January 18, 2013 4:53 PM
  • Followup to the above - I created a SQL Server view and linked to it, filtering with the [Forms]![MainForm]![PrimaryKey]  criterion. Performance slightly worse than the adp solution, though acceptable. It look slike the best solution is a native Access query with a filter on the key.

    Any explanation as to why the pass-through and  View options didn't perform as well as I anticipated they might?

    Dave Humphreys
    Vancouver, BC Canada

    Monday, January 21, 2013 5:39 PM
  • I'm in a similar situation, trying to figure out what direction to go in now that ADP is no longer supported.  

    I prefer the ADP format, because it's so much easier to just point your front end to a DB, rather than having to maintain a huge list of linked tables and views.  If you want to point a front end to a test system or move it to another server you'd have to re-create all your links.  And in man cases, it's cleaner to reference a stored procedure as the data source, rather than use vb code to get your data.

    Also if you have your data on a SQL Server backend, why would you want to do the 'middle man" processing that the MDB format does.  I'm not sure why other posters here suggest ADP is less efficient.  I didn't understand the point about "local tables" either.  You can have local tables in our SQL Server backend just as you could in an MDB.

    The only drawback I've experienced with ADP is that MS doesn't allow you to change the driver that is used to connect to your SQL Server backend, so I've had to get the data via VB code so that I could connect with the sql native client.  But that's more of an issue with MS support than with ADP itself.



    Thursday, January 24, 2013 11:36 PM
  • Hi s_solt

    Finaly they give ADPs the last shot, was about time...

    "s_solt" schrieb im Newsbeitrag news:63047220-cc64-42ba-869f-c1b127b31e1f@communitybridge.codeplex.com...

    So what are the viable alternatives to Access 2010 .ADP -> SQL Server
    2012?

    As you have a lot of stuff now in the SQL Server (stored procedures, queries and so on) it makes sense to keep the SQL Server as backend.

    * Access 2013 accb with SQL Server linked tables
    Path of least resistance. Don’t imagine this supports stored procs etc

    That's the way to go. Of course ACCDBs support stored procedures, views and so on. Only with functions they have a little bit a problem, but you can workaround it easily by just building a view around the table values function. You also may use passthrough queries to execute code on the SQL Server without JET even checking it and getting resultsets back if you need it.

    And you have everything what you need: same rich UI, similar designers, came coding environment and so on. And with newer Access versions linking to Sharepoint Lists is well integrated, too.

    What you want more?

    Henry

    Friday, January 25, 2013 2:35 AM
  • Hi Berry

    "BerryVolley" schrieb im Newsbeitrag news:26c82693-66af-47a1-8417-630a32198f0a@communitybridge.codeplex.com...

    Do you still think that linked tables is the way to go?

    Yes, it is. I've seen much larger projects in MDBs than yours. DAO and ODBC is the old and newly again recommended way of MS to get data from SQL Server into Access. Fast, reliable and fully supported.

    Henry

    Friday, January 25, 2013 2:37 AM
  • Hi Berry

    "BerryVolley" schrieb im Newsbeitrag news:16a807e8-aee3-41ef-8300-3178ba8fd972@communitybridge.codeplex.com...

    Is Access 2007 ADP fully compatible with SQL Srv 2012?

    How could it be?!? A2007 came out years before SQL2012 and I doubt highly that the SQL2012 PG gave a sh* about backward compatibility to ADPs that are announced to disappear since years.

    If not, should we move to Access 2010 ADP to achieve maximum
    compatibility? What are the pros of using Acces 2010 ADP compared to
    Access 2007?

    IMO no. You should move to A2010 or later ACCDBs and link the SQL Server tables with ODBC to your application. If you don't move now you will have to move later as ADPs will disappear. Fact.

    Henry

    Friday, January 25, 2013 2:41 AM
  • Hi Heimdal

    "Heimdal Data AS" schrieb im Newsbeitrag news:ff84119f-2777-44ca-ad75-99616de4608e@communitybridge.codeplex.com...

    It will cost .5 mill dollars to convert/rewrite into eather .NET or
    ACCESS/ODBC. Not realistic

    Did you ever try to migrate to .NET and to Access/ODBC? I doubt it as you else wouldn't say it costs about the same amount. Try once just to migrate one form to Access/ODBC (import it from the ADP and change it to connect to the linked table, build queries for the stored procedures and link view as tables to your ACCDB). Then try to do the same in .NET with a similar rich userinterface and you will recognize what I write about. You will have to rewrite everything, design the forms from scratch. You will have difficulties to create similar reports and you will have to deploy applications, frameworks and assemblies to your clients, not only request Office Pro being installed.

    If you made a pilot migration to .NET and came to a final end of .5m$ then the Access/ODBC migration would be far less than .1m$. And this would be an investment into the future and at the same time a protection of your investment in accumulated Access knowledge and Access licenses and deployment infrastructure.

    Henry

    Friday, January 25, 2013 2:50 AM
  • Doesn't really answer the original question, which is basically, what if you have an existing project that you want to convert to Access 2013?  The only Access development I have done in the last 10 years IS IN ADPs!  Where have Microsoft ever stated that you shouldn't do that?  When?  And who made this stupid decision?  When it came out, it was THE recommended way.  I don't do linked tables any more!
    Wednesday, February 27, 2013 11:58 PM
  • Albert: you still haven't really answered the question.  So, for all the users who have existing ADPs, you are saying stay with Access 2010 or prior versions, because Microsoft, without prior warning, have discontinued a widely used method of database development.  
    Thursday, February 28, 2013 12:01 AM
  • Sure, I think that is about the best advice. - consider the 2010 runtime.

    Remember, it is SQL server that is dropping oleDB support. So having a "new" version of Access ADP when SQL server will not support oleDB kind of begs the question as to why continue spending money on a feature that SQL server does not support anymore?

    So, it really SQL server and oleDB that being dropped as the real basic issue here.

    And no, there not like there has been "no" prior warning. The industry much started to leave oleDB as a basic technology MANY years ago.  Was there any "industry" warming that the DOS version of dBase was going away? (I don't recall as such).

    However, announcements by Microsoft started on this issue at least 2 years ago in 2011.

    And keep in mind that oleDB, is a propriety system where as Open Database connectivity is a INDUSTRY standard, and a OPEN one.

    The future support as stated has been announced here. The future as stated is Open Database connectivity.

    So people have been given ample time and warming here.

    But, for those existing setups?

    I do think using 2010, or even using the Access 2010 runtime is a good choice. It not like you can't use that runtime for say the next 10 years (so 23 years total time frame here is a beyond most products I can think of for the desktop).

    As I pointed out, we are now into the 13th year for ADP's as I write this. Even 13 years is LONGER than most other products in that same space.

    However, as  noted I fail to see why one cannot use the 2010 runtime for the next X number of years.

    While some advantages of ADP exist, the clock is ticking, and using an open standard is really the best future road here.

    And in the case of Access, using open database connections to SQL server works well and has several significat advantages over ADP's anyway.

    So the set of cards are being dealt now. And people have ample time to plan for SQL server's phase out of oleDB support.

    The fact that you can use the Access 2010 runtime in 10 years from now will mean LITTLE since SQL server will not. There is lots of time to deal with this issue - likly more then in most cases of technologies.

    Best regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, February 28, 2013 12:26 AM
  • So, I've written an app in Access using ADP.  I then say to my client, oh, sorry, you can't move to Access 2013, because it doesn't support your app.  Oh, and you know how your computer crashed and you no longer have a license for Access 2010, tough.  So, no upgrade path for ADP files?  I can't simply open it up and have a wizard do the translation for me?  Imagine if they did that with Excel files.  There would be an absolute mutiny.  I have attended development conferences and never heard a word about the discontinuation.  I'm not a full time developer, but I spent thousands of hours developing a very sophisticated scientific analysis system, and so now no path forward.  What you're suggesting is complete crap.  I have not heard or seen one word about the discontinuation of support for adps!  And I think by the the above, neither have a lot of people.  Microsoft have just completely crapped on thousands of Access ADP developers.  Nice.  No way to automatically convert a file. Shame if you just bought a new computer and now only have Access 2013, and you can't even open the files!!!

    If linked file databases were the way to go, why did Microsoft have a wizard to migrate FROM accdb to .adp files?  

    • Edited by gjkiwi Thursday, February 28, 2013 1:22 AM
    Thursday, February 28, 2013 12:28 AM

  • >>If linked file databases were the way to go, why did Microsoft have a wizard to migrate FROM accdb to .adp files? 

    Why not a wizard to convert to web? Or how about a wizard to conver to a DOS program? Fact is you be using DAO and not ADO – apples and oranges. It just not that simple. It is called change.

    The use of oleDB being a technology on its way out has been occurring for several years.

    You simply thus need to keep up on software trends on what is occurring in our industry.

    You might as well then purchase a home dental kit and start working on your employees to save money that way. And if dental technology is changing, then you better be aware of changing trends.

    So the doctor who using older practices, older medicine and hurting patients is to blame everyone else but themselves?

    If such a large investment on your part is occurring, then you better spend the time and money on keeping up to date in this industry.  You have to take on that responsibility.

    If you are attempting to write software of value in this industry, then not having concern for lifecycles on your part means you are going to get caught out

    >>and now only have Access 2013, and you can't even open the files!!!

    The above is VERY bad idea. You NEVER UPGRADE to the v-next version of a software development platform without LARGE amounts of testing and planning. 

    For something simple, and of little value, then sure, you might jump to the next version of using a software development system. Remember, you are developer of software here – not just opening some document.

    It is a high risk adventure to roll out software to the next version of ANY development system – especially something with any degree of complexity that you claim. 

    And as I noted announcements of oleDB being on end of life is now common knowledge.

    http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

    I most happy to accept that some are not happy about the demise of oleDB in our industry. And I am most happy to accept some are not happy about the demise of DOS and dBaseII

    You simply have to take responsibility for writing software and be aware of what technology changes are occurring.

    If you going to write software in this industry and be a developer, then you have to take on the corresponding education. No different than that dentist, or the person fixing your photocopier.

    The idea that you would upgrade out of the blue a large complex application to the "next" version of ANY DEVELOPMENT system is a very poor practice.  (I am being kind – in most developer circles this would be consider much worse!).

    To upgrade any software application with development dollars product to the next version takes CONSIDERABLE effort and testing and planning.

    Any time you have important software running you don't just toss it on the next version of windows or make some act of blind faith and jump to next version of windows or the "next" version of product X.

    Our industry has NEVER worked where you don't plan based on trends.

    I just have to say this approach to software development is a basic and practical and COMMON knowledge of anyone who been given the basic task to develop software of value with any degree of knowledge.

    Now of course I fully feel for you.

    And I accept that the above is little consolation for your case.

    Your real problem is SQL server is dropping oleDB support, not that Access is since EVEN if Access continues with oleDB support, it will not work with future versions of SQL server.

    However, at the end of the day, I see little reason why you cannot use the free runtime for the next X years while YOU PLAN your v-next version based on industry changes. You have LOTS of lead time here.

    In fact I don't see why the 2010 runtime would not suffice for the next 10 years.

    Best regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, February 28, 2013 2:16 AM
  • Wow, interesting. 

    Look at these for comments:

    http://accessexperts.net/blog/2012/11/16/adps-are-dead-in-access-2013-what-should-you-do-about-your-adp-app/


    http://social.msdn.microsoft.com/Forums/da/accessdev/thread/ba30cfaa-58ed-4139-8391-88e94fb4e87c

    Read the last comment by Aaron Kemp, DBA,

    "wow, totally disagree.

    With Access Data Projects, you get a FILTER in addition to a SERVER filter.

    it's REALLY REALLY REALLY powerful, and only a retard would use Jet to get to SQL Server data."  


    Thursday, February 28, 2013 3:50 AM
  • I'm not getting into the middle of this, but I will say this:

    You can take anything Aaron says with a grain of salt.  His only comment in any Access forum has always been negative, which only goes to show his limited level of knowledge on the subject.  Like any program/tool, there are pros and cons and you have to recognize them and know when to switch programs/tools, but there is a reason why Access is the most used database in the world!

    Also, Albert is a highly knowledgeable developer and is merely trying to help you and the original poster out.  Being rude achieve absolutely nothing.  If you don't like his advice, then simply ignore it.  But he has much more experience and knowledge then most Access developers and I would cherish any information he takes the time to share with you/us.  I know I do.

    One way or another, I do wish you luck with your project.  I know it is very frustrating loosing ADP (you're not the only one facing this dilema).


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, February 28, 2013 4:33 AM
  • And again, I ask, how does one open up an ADP file to extract the code, if one doesn't have an earlier version of Access?  For example, if you were to open Excel 2013 and then to get a message that you couldn't open a previous version of a file, you'd be pretty annoyed.  What I'm saying is, there is no conversion tool built into the system.  So, your computer crashes, and you only have Access 2013.  What then?  Imagine someone writing a novel in Word 2010 and then being told, oh, sorry, we don't support that file version any more.  Sorry, you'll just have to start again.  ADO was supposed to supercede DAO, and now, after all of the information saying how good it was, and that directly connecting to SQL, and being able to edit the SQL tables in place, suddenly, that is no longer valid?  And you can't "access" the file any more?  The whole point of Access is that it is any easy to use platform, that could access most forms of data.  Then Microsoft said, hey, look, we have a tool which will allow you to upscale to a SQL backend and we will even provide a new file format for you, so you can edit tables natively, get rid of that nasty DAO code and work better.  Now, because not enough people (the only reason I can see for it being dumped) wanting to use it, they have dumped on people who use it.  And they have apologists saying it was well telegraphed that it was history.  Some people have application that go back to 2003, and some have spent tens of thousands of hours developing them.  No backward file compatibility?  What the??   You're suggesting what, open it up in an older version, convert the database to accdb format and then you'll be right?  Why not just have a wizard that converts it to the new format?  Nope, too much trouble.  You may have all the time in the world, but I don't.  I don't mind that they've done away with it, I'm not complaining about that at all, I'm simply stating no way forward.  "We don't care!"  Imagine if they did that with 32-bit support.  Oh, sorry guys, when you go to a 64bit platform, you have to leave all your old apps behind.  Oh, hahahaha!  

    Thursday, February 28, 2013 4:34 AM
  • Well I not sure some nefarious comment about some filter option is enough to be the whole reason what someone would bet their future of their IT department on?

    I fail to see any real relevance in that comment. I suppose we can drop this to a childlike discussion here, but we not going to solve anything here by doing that.

    Using JET has a great number of advantages – one of which you can have local tables and local processing that takes advantage of local CPU. And with pass-though, I fail to see any real issue in regards to some silly filter option.

    Hardly an issue to bet the farm on here?

    I recall many people thought change from DOS/text based to graphical (GUI) was a fad. They waited WAY too many years to plan for the changeover to GUI. In fact many I know stopped writing software in this changeover. (their skills become obsolete).

    Either we behave like adults here and educate ourselves as to the changes and trends in this industry or we don't. And we have to deal this issue.

    As I said, this change is underway. We can stick our heads in the sand, or try to kick this can down he road and ignore this (like those folks who ignored the GUI change).

    I see little reason as to why one does not use and adopt the 2010 runtime for the next X number of years. I feel this choice gives one YEARS to plan for such a change over.

    If one does not start planning now, in in 10 years EVEN if you use the 2010 runtime, the problem is SQL server will not be supporting oleDB. Then you really be in a heaps of trouble.

    So, one has ample time to deal with this issue, but that means a strategy has to be adopted here.

    And some time honored quotes on planning:

    When planning for a year, plant corn. When planning for a decade, plant trees. When planning for life, train and educate people.

    -

    By failing to prepare, you are preparing to fail.
    Benjamin Franklin (1706-1790) American statesman, scientist and philosopher.

    -
    It takes as much energy to wish as it does to plan.
    Eleanor Roosevelt (1884-1962) American columnist, lecturer and humanitarian.

    So the time is now – you have some leeway here.

    Change in the software industry is not new, and sometimes it bites us.

    The trick is start taking steps NOW to ensure you have the skills and education to continue being relevant. And by planning now, you not get caught out in a technology change.

    So while there are NOW some easy ways to "kick" the can down the street such as using the runtime for the next 5 years, during that time, some 5 year plan should be adopted to deal with this issue.

    You have to start planning now, since eventually the ADP choice will have less and less options and greater consequences if one does not plan now for this change.

    Best regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, February 28, 2013 6:09 AM
  • The main problem here is that I have an application which is working perfectly fine and Microsoft is breaking it.  I can tell my people not to upgrade to Access 2013, but I read about possible issues with SQL 2012.  Sure for whatever number of years it's been considered best form to use linked tables.  I have been using linked tables for newer projects, but I have three databases which I developed back when adp first came out.  Are you trying to tell me that when Microsoft added adp to Access 2000 that they told us not to use it because it was no good?  I'm pretty sure that it was faster than using linked tables back then?  I still run into the case where if I do a join between two tables in Access in a linked table scenario that I get very slow performance and have to move that join to a view and link it into Access.  If Access were truly capable of converting that query to T-SQL then I don't understand why the slow performance.  I did that recently and will go back out of curiosity just to see if I can understand why Access couldn't have done that.  I was definitely not using VBA.  

    Oh well I guess my complaining will do nothing.  I'll just have to waste my time converting these things.  I just hate that there is absolutely no value added.  The apps work fine so why make me fix them

    Saturday, May 04, 2013 10:48 AM
  • Actually the reason for my looking at this thread is that I have a bunch of forms with subforms.  The subforms are bound to stored procedures.   What is the most efficient process to convert these back to a linked table Access db?
    • Edited by alderran1 Saturday, May 04, 2013 10:51 AM
    Saturday, May 04, 2013 10:51 AM
  • Create a passthrough query that calls the SP and then bind the form to this query

    Nobody forces you in the linked table architecture to only access these linked tables.

    Performance is better this way but forms are by nature of SPs read-only.

    Henry


    Saturday, May 04, 2013 2:06 PM
  • This seems to be the most active thread on the discontinuation of ADP in Access 2013, so I thought I would post my commentary.  What a horrible and utterly disappointing idea, I think, this is.

    A much more balanced approach would be to start with the removal of the SQL Upsizing Wizard in Access 2013. 

    It would discourage new ADP development, and give those of us that have some ADP apps out there a fighting chance, to get things in order for the next revision of Office.

    I have found that the people in smaller to midsize organizations, the one where access is probably more often used, are acquiring workstation/laptops from Dell/HP/Lenovo ect … usually preloaded with MS Office, as the Microsoft Volume licencing is usually more expensive than the OEM preload by a considerable margin, and the MSVL is an absolute nightmare to deal with.  If you never dealt with MSVL consider yourself lucky.

    For this size of business, it is crazy to think they are really going to be doing a thorough inventory of application requirements beyond “We need Microsoft Access”

    If future versions of SQL are not going to support oleDB  fine, but if SQL 2012 does and that means that probably 99.999% of SQL Server instances out there that are being utilized support it.

    If you have an ADP app running on SQL server chances are you are going to be running that server for a few years. When the next Rev of SQL comes out, my guess is 0.001% of the servers to be upgraded will have an ADP app

    Working with Access in an ADP form, as a SQL Server centric developer, is such a more pleasurable experience. The link tables ODBC that some are proposing as a valid argument is just trying to polish a turd in my opinion

    -Views and Stored Procedure support… to start , really now, you don’t use SPROCS, if Access MVC’s have never experienced a situation where a performance benefit from a Sprocs has been realized … no comment

    -being able to leverage proper TSQL syntax with decent aliass and recursive queries, deletes with joins  ect.  and not having Access puke … SQL Spatial data types, I have a fighting chance with ADP, sure I can create secondary  ADO connection to the database and run some proper TSQL syntax rather than the contorted JET syntax, but being able to leverage :

    set rs = currentproject.connection .execute(“Select … “)

     in a pinch was so nice.  Call me lazy, this is Access, I’m using it to quickly get something done. 

    A point others have brought up, setting up a dev and production environment  and being able to simply change a connection string in one place, much like you would in deploying a .Net app, linked tables are horrible to deal with.

    All that encompasses the almighty connection string, and leveraging ADP as a middle stepping stone, I have found incredibly useful and is an important part of developing things from a quick and dirty stand- alone Access app to a full blown .Net application.

    For the organization I work at, we are about to implement and MS Office  Standardization.  I am pushing for Office 2010 Pro+ to be that standard, we have a mix of 2007, 2010, a few 2013’s now and (shudder) the odd 2003 kicking around.

    There are some great things in office 2013, that I have tried out (additions to power pivot, geoflow) that bleeding edge and some Office users may use, but there are some pretty large features that are being stripped out that make me ask:

    -“What the F… are they thinking?” 

    -"How much do office developers actually use Office"

    -"What kind of users are opting in for submitting usage info/stats?"  on this, my guess, a lot of power users are probably opting out of the user stats submission process,  resulting the decisions being made.

    ON a tangent, another feature being stripped or massively crippled is the Journal in Outlook. 

    Never heard of it, not surprising it’s a feature that is tucked away. And because it was tucked away no one uses it.  And since no one uses it, MS is going to axe it, and have sliced off a big part for 2013.  I crunch a lot of data from a lot of sources; having the journal log every time I opened an Office document has been extremely helpful.  It is one of those features that when I show someone else they almost lay an egg.


    • Edited by Berwyn Friday, May 10, 2013 4:45 AM
    Friday, May 10, 2013 3:59 AM
  • Also noticed some commentary about using ADO vs DAO 

    Microsoft Data Access Technologies Road Map (last update  Sept 2011)

    http://msdn.microsoft.com/en-us/library/ms810810.aspx

    DAO is mentioned, in the "Obsolete Data Access Technologies" section

    using ADO in access VBA  isn't exactly the same as ADO.net in VB  but its pretty close, even if you go the C# route in .net, things are a lot simpler if you are already familar with the ADO object model , in my opinion.

    I haven't touched DAO in 10 years, when Microsoft stopped development and declared it dead.

    If there has been a second coming of DAO, i haven't seen any docs on it.

    One thing I'm worried about with the demise of oledb  is support for the ADO SHAPE command with SQL Server.

    I have only ever used it with oledb. Another thing that mad ADP files nice.


    Friday, May 10, 2013 7:11 AM
  • well some Google results found some disheartening info

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms676105(v=vs.85).aspx  

    Really this is oledb demise is a step in the right direction?

    Given my luck, next thing to go will be support for spatial data types in SQL Server.


    • Edited by Berwyn Friday, May 10, 2013 7:25 AM
    Friday, May 10, 2013 7:22 AM
  • Unfortunately I've joined this discussion rather late.

    "His only comment in any Access forum has always been negative..." seems a little harsh considering Aaron's comment was to state a feature of ADPs was "...REALLY REALLY REALLY powerful..."

    "Being rude achieve absolutely nothing" - completely agree.

    What perhaps should be recognised by MVPs though, is how condescending they can come across & that they can sound a little "brain washed" into singing the Microsoft tune.  It is all well & good to preach on future planning, etc. but not all of us have the luxury of time to study trends in coding, the new technologies, etc.  Many of us are merely responding to a 'manager' demanding a piece of software yesterday & "we've got no budget for this".  Such constraints require you to produce the best product you can, in the quickest time, using your existing knowledge, with no allowance for research & no training budgets.  It is these 'realities' that Microsoft seem to spit in the face of.

    There has been comment that no-one is 'forced' to upgrade.  I would suggest Microsoft's policies of:  ending support on older products, cessation of security updates for older products, licensing restrictions on previous versions, online supply of downloads rather than physical media & preinstalling the latest versions on new hardware - all come together nicely to effectively leave users no choice but to upgrade.

    Wednesday, July 17, 2013 7:06 PM
  • Excuse my late arrival, and also if my comments sound equally "childlike".  Personally I find your dismissive tone of the "silly filter option" unfair on the OP.  Especially as I read neither "...bet their future of their IT department on" or "an issue to bet the farm on here" in the OP's post.  The very valid point is the loss of what they saw as a strength of the software, with no replacement in the 'alleged better way'.

    Your trite quotes appear to push the blame onto developers for failing to plan.  My opinion is that a lack of planning is often not from developers, but their employers.  Lack of budget, short-sighted management & impossible deadlines leave developers with little choice but to utilise the technology they already know.

    I believe people already are behaving "like adults" by not pursuing the latest Microsoft "fad".  The latest information I read indicates Microsoft are now moving to a yearly cycle for software like client OSs, browsers, etc.  Does Microsoft really think that companies still in the grip of a global recession have the time, money & resource to keep implementing change, after change, after change; including the extensive testing required each time?

    Wednesday, July 17, 2013 7:35 PM
  • I don't know where people are getting some of this information from. ODBC is clunky and slow. Unless you learn the tricks like the cheesy pass through queries and linking views as tables to improve performance. While I would agree that I have seen more ODBC databases versus ADP, ADP is much easier to work with and has much better performance and reliability without having to resort to programming hacks to improve performance.

    It's a shame that Microsoft neglected to warn anyone on their plans to remove support for ADP. Yes, .Net is a much better platform to develop on. Yes, there are many better development platforms to Access. Problem is, not every company has the larger budget to support it. Access ADP files used to be a nice go between where software can be developed quicker than in .Net and other platforms, and it was still a fast and reliable solution for small companies with small budgets. This budgetary problem is pretty common in small businesses. Which makes me think that Microsoft has taken another step to abandoning small businesses.

    I won't ever recommend Access for an application again unless it is an application where it's just 1 or 2 people using it. I used to develop ADP projects with ease that were capable of supported simultaneous users in the hundreds. Bad move to drop support for this Microsoft.

    Saturday, August 31, 2013 1:18 PM
  • Well...after reading through the thread - or rather, laughing my way through the 20/20 hindsight's that are being presented as (I hope, jokingly) "at the time best practices that everyone knew.....except YOU, apparently" I can state with certainty that the answer to the initial post is "I don't know...I'm having this problem, too.  But I do know a huge bonus - that will move any of the platforms I'm considering, "toward the top of the list" - will be if the word "Microsoft" rears it's head once in the product's documentation: under "OS Compatibility"

    Beyond that, every occurrence of the word "Microsoft" will be yet another 100 pound weight thrown in a sinking boat.

    This product cycle's a complete disaster...and it's not over: Amazon is destroying MS purely by proximity. The talent drain from the Eastside to South Lake Union is....stunning.

    Access run-time for another 10 years? lol.....that's not on the menu....I'm trying to pin-down my bet on which unit keeps the MS brand when the Windows and Office groups part company.

    Wednesday, September 25, 2013 6:53 AM
  • Also... after reading through the whole article, this is my conclusion:

    - If you have an adp with 1000's of stored procedures (my adp generates it's own intelligent temp stored procedures and sql commands)

    - If you have 100's of tables in the back-end

    - If you have 100's of forms, buttons, combo-lists, reports based on pure sql 

    like I do

    And you have it measuremade to 10's of clients

    like I do

    you don't want to go to ODBC. You want to keep using your very superfast direct T-sql commands, and even call the merge replication from your adp because a lot of machines run stand-alone with sql express.

    tried to link the tables, it worked, up to 50 tables, then access crashed, became slow, the accdb became HUGE

    In the end: I use office 365 and the good old access 2007 together with the recent version of SQL server.

    If I change: it won't be with access, because maybe in 10 years, I will have to change again?

    Johan


    • Edited by AAnscharius Thursday, October 10, 2013 6:26 PM
    Thursday, October 10, 2013 6:24 PM
  • I've used an adp/SQL Server application for many years and it still works well.

    I'm now at a crossroads what to do next with this new project (in telcos).

    Ideally, a webbased .NET solution would be great, but time (and experience) is not on my side.

    Now I know from experience that ADP's using Access 2010 against a SQL Server 2008 is doing abolutely fine.

    I chose ADPs because, from experience, linked tables are absymally slow especially with remote work on a VPN. Horrendous. I also chose them because they automatically login to the SQL Server with the correct permissions and the correct objects to view. I also use completely stateless forms (unbound) using ADO, so very fast.

    My questions are:

    1) Do ADPs work against a SQL Server 2012 instance? Has anyone tried?

    2) If someone's got Access 2013, can they also use Access 2010 runtime to run a compiled ADE?

    3) If we should be migrating to acddbs, how does the whole linked table management piece work? For example, if someone's got a specific set of permissions against the backend, how does an accdb link the required tables? I certainly don't want to write additional code to link the correct tables.

    4) If the backend tables are altered, does the accdb automatically reflect this? My experience of linked tables is that they have to be refreshed, and that does not always work as planned.

    5) Some people are saying that linked tables are as fast as sending a SQL query via ADO to the server. I'm having difficulties believing that.

    If I can stick with ADPs a bit longer, I will.

    Friday, November 01, 2013 4:17 AM
  • I can answer question #1- I've been using Access 2010 with an ADP connected to SQL Server 2012 and everything works fine. As for the rest of the questions, I'm stalling as long as I can.

    Paul

    Friday, November 01, 2013 8:56 PM
  • you don't want to go to ODBC. You want to keep using your very superfast direct T-sql commands, and even call the merge replication from your adp because a lot of machines run stand-alone with sql express.


    Won't pass-thru queries perform just as fast ?
    Sorry, I got into this a bit late..
    Monday, November 04, 2013 1:22 PM
  • I can understand the frustration you must be going through.  Access 2013 team decided to get away from ADO to DAO by adding greater number of features but forgot to look into the role of a SQL Server as a scalable product with great number of other programmable features and data transformation.  SQL Server complies with SQL recognised by large databases complying to ANSI 92.  It is astonishing that Microsoft has not made Access 2013 compatible with SQL Server! SQL in Access is not the same as SQL in SQL Server (standardised), so if your adp legacy system does not migrate to ODBC using DAO (client-side) then don't be surprised  :(

    Access 2013 does not support BigInt and maps to test.. lol

    If view on your SQL Server properties is not bound to schema then the data type will be mapped to text.

    "Table-value functions" with parameters seems like not supported in Access 2013, I am still stuck in these

    Stored procedures not supported in Access 2013

    Oh by the way ... I did like the adp project as it had the power to develop SQL Server based solution with all its features in a desktop environment... That was impressive

    DoCmd.RunCommand acCmdConnection does not work

    The list goes on... I feel sorry for you if you are migrating a large old legacy application where you can not afford to rewrite!


    • Edited by Kumar Sinha Sunday, November 17, 2013 7:35 PM
    Sunday, November 17, 2013 7:32 PM
  • Let me give a word of caution on the linked tables strategy.

    Although it certainly offers a great deal of benefits and ease of use, when you tap into the JET engine via DAO you will begin to see some performance issues when you scale out your project.

    I work for a company that has developed several databases for a fairly large distributor of goods.  They use Terminal Server to handle most of their employee connections.  Once you get around the 25-30 people mark, the server begins to peak out it's CPU and about 95% of the usage comes from the Access JET engine.  The server becomes unresponsive to all requests, even just trying to open a file can take minutes.

    We have had to completely retrofit our VBA to utilize ADO and drop most recordset usage altogether to reduce the terminal server load.  The more Stored Procedures, Views, and queries you run on the SQL server the better.  In fact now I avoid using local Access tables altogether so as to avoid using the JET engine.  

    I have also seen a dramatic speed improvement using this strategy.  A process which took 3:30 - 4 minutes previously now takes 45 seconds to run.  This process involves linked Oracle servers as well.  Trust me, when you're linking data from Oracle, SQL, and Access and using the JET engine to do all of that.... you might as well just plan on another $10,000 for your tech equipment budget.  Or do what I did and use the SQL server via ADO.


    Ruler of the undershorts

    Friday, November 22, 2013 4:51 PM
  • Enrico,

    Albert is one of the world's best Access developers.  I have been following his work for close to 10 years now.  If he says it, I will probably not challenge him, but I might ask for clarification.

    ADPs were another bad idea Microsoft came up with.  When they first appeared, I decided to try it and soon realized I could find no real advantage to ADPs, but I did find enough constraints to make me put the idea to bed.

    And, if ADPs aren't a bad enough idea, they use ADO, another bad idea for Access, but useful for SQL Server.

    Friday, November 22, 2013 6:19 PM
  • Once you get around the 25-30 people mark, the server begins to peak out it's CPU and about 95% of the usage comes from the Access JET engine.  The server becomes unresponsive to all requests, even just trying to open a file can take minutes.

    We routine deploy and have setups with 50+ users and we find no such problem with JET and CPU usage.

    The above is like saying that dcount() is slow when in fact it is the SAME speed as writing SQL to do the same thing.

    The REASON why dcount(), dmax() often gets a bad rap is because someone comes along and uses that function INSIDE of a SQL statement. I mean if you use a VBA or the Access aggregate functions inside of a SQL statement, then the statement runs slow and such functions run over and over for EACH ROW. The issue here is NOT that function is slow. The simple matter is you are running the function MANY times and that is the REAL problem.

    So the function is not slow, it is the POOR CHOICE of the developer of knowing when to not use such functions is the issue. The result is then the community here states that dcount() etc. is slow and should be replaced with SQL when that is NOT the case! (they run the same speed – both produce SQL!).

    We have had to completely retrofit our VBA to utilize ADO and drop most recordset usage altogether to reduce the terminal server load. 

    Well, actually you did not. Such a choice was not a "YOU HAVE TO" kind of choice. This choice was based on YOUR experience and you choosing to adopt ADO here.

    The REALLY big issue here you have to ask is when did you learn or realizes that new versions of SQL server will not support oleDB and thus it begs why are you choosing ADO that the industry is dropping?

    The simple solution in most cases and at the lowest cost is to utilize views for those queries with complex joins and of course that of aggregate functions.

    And for pass-through queries the most simple approach is to use DAO querydef objects. This is less effort, more future proof and MOST important is LESS COST for most applications then introducing ADO into an Access application.  So you wind up with the same performance as your ADO suggestion anyway.

    The only reason I can think of to adopt ADO pass-though queries at this point in time would be if the original application was using ADO, but suggesting at this point in time to adopt ADO or introduce ADO when it not needed much amounts to very bad advice, if not an outright suggestion to waste your client or employers' money.

    For a NUMBER of years the suggesting from the industry is that Open Database Connectivity is the way forward.

    And now that end of life (oleDB support) of ADO been announced FOR QUITE SOME TIME why would one introduce a technology into an Access application that in a few years will have to be replaced with DAO and pass-through queries? This certainly opens up the door for you to grab another round of billable hours for the client or your employer, but the real issue here is that adopting ADO as a solution at this point in time makes little sense give the state of affairs in the industry.

    Is there any particular reason you introduced ADO as opposed to using DAO pass-through querydefs which offer the same performance?

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Friday, November 22, 2013 7:12 PM
  • Access 2013 team decided to get away from ADO to DAO

    No, Access 2013 still supports ADO just fine. The WHOLE industry has been moving away from ADO for about 10 years now (ever since .net came out).

    The problem here is that ADO is on its way out. SQL server not going to support it in the future and as noted .net developers started dropping ado about 10 years ago. It is a simple trend.

    At the end of the day if you want to use a 20 year old version of FoxPro, then you can. Nothing stopping you. However such products don't need the needs of the marketplace anymore.

    I been doing Access consulting for over 10 years now and NEVER had any paid work using ADP's (never). And I done more Access back end to SQL migrations then I can remember.

    And more amazing?

    Well those SQL migrations and older applications continue to be supported today and continue to run just fine.  The sound choices I made are still future proof and still viable solutions.

    I mean if the .net community be moving away from ADO and SQL has been moving away from ADO, then are you going to stand here and suggest that someone now adopt ADO for an Access application? (that is just a brain dead suggestion).

    A .net developer who adopted ADO is in much the same boat here. That technology is on its way out.  You have to explain what good is adopting ADO when SQL server is moving away from that choice?

    You have to be a practical person here. You might love windows 3.0, but regardless of how much you like that old 16 bit system, it would be irresponsible in public to recommend such a choice.

    You might have loved the open source Apache 1.1 web server, but the community left that system and the support of the system long ago (and adopting newer versions would mean your old code would break). However, if you want to use windows 3.0 and FoxPro, you STILL can. And if you want to use Apache 1.1 as your web server, you can. However such recommends given the progress of this industry would make such recommends REALLY poor suggestions because the industry moved on from those platforms.

    The simple question + answer here is what is the practical and prudent choice when using SQL server with Access?

    Simple answer:

    The PAST LONG TERM MOST POPULAR choice REMAINS the best future choice. That simple choice is to use what MOST developers have been using. That is to use the DEFAULT Access object model and linked tables.

    Access JUST received support for the Azure version of SQL server. So there are new bits and parts being added to Access to support SQL server, but those choices don't involve ADO technology and also don't involve ADP's projects which are based on a technology that been moving out of this industry for 10 years now.

    Access 2013 does not support BigInt and maps to test.. lol

    Mapping to text is just fine in near all cases. Even in the RARE cases in which you require users to edit the value then editing that column as text is allowed anyway. I just don't see a problem here.

    "Table-value functions" with parameters seems like not supported in Access 2013, I am still stuck in these

    Give me an example of what you needing to do. I have not found this to be a problem.

    Are you looking to call/use the function from VBA or a query?

    Assuming a function that returns a table, say this:

    CREATE FUNCTION MyTable1  (@nID bigint)

    RETURNS TABLE

    AS

    RETURN (SELECT * from Table1 where BigNum = @nID)

    Note in the above how we using a bigInt value as a parameter. We want the above into a recodd set. I use this:

       Dim qPass         As DAO.QueryDef

       Dim strBigNum     As String

       Dim rsResults     As DAO.Recordset

       strBigNum = InputBox("Enter big number")

       Set qPass = CurrentDb.QueryDefs("qPass")

       qPass.SQL = "select * from myTable1(" & strBigNum & ")"

       Set rsResults = qPass.OpenRecordset

    All DAO, and VERY easy code to write. And above is if anything is LESS code then a ADO example anyway.  Are you really saying the above simple and easy code stumped you?

    So above works fine to pass a bigInt parameter to a table function and return the table as a recordset in VBA.

    Perhaps you trying to do something different, but the above is trivial, easy and as noted is likely LESS code then an ADO example.

    >>Stored procedures not supported in Access 2013

    Again real rubbish.  Care to explain what you mean by the above or do you need me to post an example of how to call a store proc from DAO (and again in LESS code then what you post with ADO).

    The simple matter is one needs to adopt a few DAO pass though query's and a few other simple approaches and the issues you note here are solved with rather basic Access developer skills.

    A little bit of flexibility in one's approach goes a long way here. In fact a key developer skill to adopt is such flexibility when faced with change.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada 


    Friday, November 22, 2013 9:45 PM
  • Thanks for the kind comments.

    Actually in all fairness, I don't think ADP's were a bad idea. In fact they were really incredible. They allowed one to write 100% native SQL server applications. And those being around for over 10 years are not a shortsighted run.

    A 10 year run in our industry is a LONG time. That is longer time frame then the MOST popular Visual Basic system that Microsoft released in 1993 (talking windows versions). The last version (VB6) was released in 1998. That is only a period of about 6 years. (certainly upgrades were released later, but the base time frame is LESS than 10 years for versions).

    I think is FAIR to state that VB6 (and a few previous versions) was one of the MOST popular systems. (and less than a 10 year run). What about Reflex, Knowledge Man, FoxPro etc?

    ADP's can arguably be stated to have lasted LONGER than Visual Basic for windows and those other products I mentioned.

    So while the industry IS moving on, I would not state that Turbo Pascal, or the Reflex database system or other great products that been part of the PC revolution were bad ideas.

    ADP's have had a great run in this industry. 

    The simple matter is the adoption rate of ADP's was small. If ADP's are that great, then where is all the blogs, articles and community support here? I see some people piling in and complaining about the demise of ADP's

    Where was the ADP's community when questions were being asked in on line forums (none to be seen).

    Where was the ADP' community posting articles and singing the praise of how great ADP's are? Again, none to be seen. Hiding in some secret cave?

    The ADP's community leaves a LOT be desired in terms of community support and helping other fellow ADP's users.  The only ones coming out of the woodwork are those NOW lamenting the demise of ADP's.

    If ADP's were so great then when have all you folks been?

    Where were all those years spent as opposed to lending a helping hand to that group of users that you supposedly so much love?

    The Access community in terms of supporting and writing about ADP's was NO WHERE to be seen. And now you wonder why ADP's are on their way out? Sorry, the world and life just does not work this way. You folks could have made a differnce here. A missed opportunity.

    Where are all their blogs, posts and support of ADPs?

    I make little apologies for the demise of ADP's.

    However I still think ADP's and that concept was a great idea.

    The large downside of ADP's is of course the current trend away from ADO and the other really big issue was how tight the release of Access had to be tied to a particular version of SQL server. The release of Access and SQL server were often out of sync in the marketplace.

    Using linked tables quite much removes all that issue of what version or release of SQL server you wish to use with Access.

    At the end of the day:

    Access is still a great client to SQL server.

    Learning a few simple approaches allows one to use store procs and server side code, and this can be done using DAO.

    The results are not only great scalable performance but in most cases my examples show that the amount of code required is = or less than ADO examples.

    A bit of change and flexibility here goes a long way and allows one to build great Access applications that connect to SQL server, and ones that scale and perform well.

    I am not here to really sugar coat this issue, but at the end of the day simply point out the way forward.

    The way forward is a great and viable future.

    And in my case, my 13 year old applications running on SQL server no doubt will run for another 10 or 20 years. And I did not have to change how I been writing those applications because I made great choices in the first place.

    So yes, I do respect and feel for those caught out by this change in the industry. Some will have bite the bullet and change how they work with SQL server.

    However choices and solutions do exist.

    That great choice for me has always been Access + linked tables + SQL server.

    Best regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, November 23, 2013 8:38 PM
  • Quote: And at the end of the day, as I stated, there nothing stopping you from installing Access 2000 on a brand new windows box. So nothing from the Access side is forcing you to upgrade to Access 2003 for example. (why did one bother to upgrade to 2003 for example? Nothing forced you?). (and this is not a type-o, I do mean 2000 and 2003 – not 2010 and 2013!)

    Unfortunately for those of us in a corporate environment where the IT department insists on regular upgrades installing an obsolete version of a desktop application is not an option.

    But I'm glad to see the advice to use mdb and linked tables instead of adp, looks like I shall have to convert my application from adp to mdb.Many thanks to all who took the trouble to write reasoned explanations.

    Tuesday, December 03, 2013 10:25 AM
  • I can understand the frustration you must be going through.  Access 2013 team decided to get away from ADO to DAO by adding greater number of features but forgot to look into the role of a SQL Server as a scalable product with great number of other programmable features and data transformation.  SQL Server complies with SQL recognised by large databases complying to ANSI 92.  It is astonishing that Microsoft has not made Access 2013 compatible with SQL Server! SQL in Access is not the same as SQL in SQL Server (standardised), so if your adp legacy system does not migrate to ODBC using DAO (client-side) then don't be surprised  :(

    Access 2013 does not support BigInt and maps to test.. lol

    If view on your SQL Server properties is not bound to schema then the data type will be mapped to text.

    "Table-value functions" with parameters seems like not supported in Access 2013, I am still stuck in these

    Stored procedures not supported in Access 2013

    Oh by the way ... I did like the adp project as it had the power to develop SQL Server based solution with all its features in a desktop environment... That was impressive

    DoCmd.RunCommand acCmdConnection does not work

    The list goes on... I feel sorry for you if you are migrating a large old legacy application where you can not afford to rewrite!


    As a long time user of Access ADP files for the Reporting engine, we are extremely disappointed that the ADP abandonment stranded many users.  Here is one Huge advantage of ADP reports that other Access databases will not touch:

    Multiple users could open and print the same report;  now subsequent users get their reports failed

    It caused us to have to re-write our applications which hard-coded ADP report files.  Access used to have the best report writer for SQL server data in the business!

    Ron Taylor, CMI software, Jan 2014

    Friday, January 24, 2014 12:01 AM
  • I am not aware of this issue of multiple users being un-able to oipen a report at the same time.

    Access has worked with SQL server for a VERY long time and thus there should be  1000's if not millions of posts outlining SUCH a common issue and yet I find none. The vast majorly of my Access applications use Open Database Connectivity to SQL server and this LONG TIME suggested choice works fine.

    So I not seen or experienced any issues in terms of multiple users opening a report.

    So I not sure why you are experiencing  such a problem, but based on general industry experience and LACK of the on line postings about this issue I can only conclude this issue is much limited to your setup and approach used here.

    Do you have a sample report that reproduces this issue?

    Best regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Friday, January 24, 2014 7:35 PM
  • I'm sorry, but our first approach was to replace the current version of the applications with a usage of Pass-Through queries, Views, and Stored Procedures.  However this approach would have meant every bound form in the applications would no longer function as they had and far more code would have be overhauled so we had to abandon it.  In the end I developed a special ADO class object which provided similar functionality to how we were using DAO but now relied on the SQL server to handle the load.

    The bottom line is we were using Linked Tables to Oracle, and MSSQL tables.  The Terminal server which ran the databases was getting bogged down with 95-100% CPU usage at all times.  Consistently the MSACCESS.EXE process was taking up the top 20 spots of CPU usage.

    The process to convert only took a couple of days and the server load dropped to under 30% CPU usage because we targeted those parts of the application which were clearly taking up the most processing power.  Functions which took in excess of 2 minutes through a simple SELECT queries set as recordsets and looped through were performed in under 15 seconds via an ADO recordset.

    Had we gone the Pass-Through route, it would have taken major overhauling of the code and forms which conservative estimating put us at about 4 months.  Using the ADO class object gave us an estimate of 2 weeks.

    I'm not saying everyone should use ADO instead, more or less what I'm saying is that DAO isn't the silver bullet and in our case could have been sighted as a major problem.

    I also don't think it's a great strategy to build in functionality which could potentially cause such problems, and then say it's the developers fault for using it.


    Ruler of the undershorts


    • Edited by RingerDaMan Monday, February 03, 2014 5:04 AM
    Monday, February 03, 2014 5:00 AM
  • I don't see how linked tables would cause a report to fail.

    Ruler of the undershorts

    Monday, February 03, 2014 5:03 AM
  • "...forms are by nature of SPs read-only"

    Henry, this is completely false.  I have lots of forms and sub-forms driven by stored procs that are fully editable.

    Thursday, April 24, 2014 10:53 PM
  • Late to the party here, but ADO is VASTLY superior to DAO, especially wrt SQL Server access.  The most obvious example for me is the ability to create a command object, identify it as as stored proc, pass parameters (both input and output) to and from the server.  WAY easier, and naturally encourages moving any "heavy lifting" data processing to the back-end, where it belongs.

    All of this talk about going back to DAO completely baffles me. 

    Very disappointed (angry, actually) that MS is abandoning .ADP.  I isn't perfect, but for a one-man developer shop, it's the most productive thing I know.

    Thursday, April 24, 2014 11:21 PM
  • Again, late to the party...

    But I really would like to know ... Has ODBC evolved over the past decade?

    If it's so great, why does one have to create these ridiculous "pass-through" queries? 

    Not trying to be flippant here, but why would anyone prefer using a "pass-through" query instead of creating a very clean, readable ado call to a stored procedure where you can cleanly (and read-ably) define parameter, their type, direction, etc?  Am I missing something here?

    Thursday, April 24, 2014 11:28 PM
  • It is really annoying to read your subtle put-downs of Access Projects and ADO, both of which are NEWER and SUPERIOR to DAO, which is OLD, ANCIENT technology.

    I really think MS dropped the ball here (seems to be a troubling trend) and I really hope they come up with a modern programming solution that supercedes both ADO and ESPECIALLY DAO.

    Thursday, April 24, 2014 11:34 PM
  • I certainly have no intention of some kind of put down of ADO.

    And in terms of an intellectual discussion pointing out facts is not a put down of anything here.

    >ADO, both of which are NEWER

    No, sorry that is NOT the case anymore because ADO is not receiving ANY new features while the NEW version of DAO CONTINUES to receive NEW features.

    The fact that ADO came out “later” then DAO is rather meaningless since ADO is not receiving new development dollars or new features like DAO is. So in this context stating that ADO came out at a later time frame is of LITTLE value and of LITTLE merit and LITTLE help in this discussion.

    In fact it been a good number of years since anything was added to ADO.

    It is a SIMPLE fact that the new DAO library (ACE) is receiving MANY new features and continues to receive development dollars.

    For example the new DAO library has bits and parts for SQL Azure while ADO does not.

    For example the new DAO library has bits and parts for SharePoint and ADO does not

    The new DAO library also now has store procedures and table triggers.

    And in that SAME time frame ZERO has been added to ADO and WORSE is the end of life support for ADO + oleDB connections to SQL server was announced.

    It is a simple FACT and observation that the .net community started dumping ADO nearly from day one (over 10 years ago). And PLEASE don’t confuse ADO.NET with ADO – they are VAST different systems.

    I am simply stating that ADO is on its way out and is being dumped by the developer community. This is a statement of fact and observation and not some kind of “put down” of ADO anymore then calling the sky blue. The sky being blue is a fact and again attempting to shoot the messenger here has little or no merit in this intellectual debate.

    In case you failed to read my posts here or are in general living in some sealed cave and not aware of what is going on in our industry I will re-iterate some points here:

    DAO has been superseded by a new VERSION of DAO that is NOW part of Access. (the ACE database engine).

    That means for the LAST 3 versions of Access (A rather LONG TIME) YOU DO NOT use nor set a reference to DAO, but you use ACE.

    Again: we write + use DAO code in Access but you DO NOT set a reference to DAO anymore (you user the newer version called ACE).

    ACE continues to receive NEW development dollars while ADO does not. ADO as noted is rather old and is NOT being supported nor adopted by ANY developer community.

    To state in public that ADO is some kind of good choice right now is simply bad advice.

    >All of this talk about going back to DAO completely baffles me.

    No that NOT is what is occurring at all. What is occurring is the general IT industry and that includes BOTH the .net community, the Access community and the SQL server community ARE ALL EITHER DROPPING support for ADO or moving away from ADO. It really that simple. Most of us never left DAO when using Access anyway.

    The result is ADO is thus now a DEAD end choice. So this moving away from ADO has been occurring for a very long time. We can get into a debate if this is good or bad, but at least get your basic facts correct.

    >The most obvious example for me is the ability to create a command object, identify it as as stored proc, pass parameters (both input and output) to and from the server.  WAY easier

    Way easer then what?

    Here is code in DAO that pass a given long value in a variable and RETURN a value in DAO to another variable.

       CurrentDb.QueryDefs("qPass").SQL = "exec GetLast " & lngIntID

       lngReturnValue = CurrentDb.QueryDefs("qPass").OpenRecordset()(0)

    I COUNT a GRAND TOTAL of two lines.

    Now your turn – post the same code with Access + ADO.

    So I have a total of 2 lines of code. And it ONLY one line of code if we not passing any values to that store proc.

    Your turn:

    Now you share you code using ADO + Access. Note how the above does not need a connection string since we are using a saved query def that is saved as a pass-though. So the DAO querydef object contains the connection string.

    Can you really post something that pass + returns a value to a store proc that takes only TWO lines of code in ADO?

    The recommend future technology path and solution for Access + SQL server is thus using linked tables and DAO (the new ACE version).

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Friday, April 25, 2014 5:00 AM
  • But I really would like to know ... Has ODBC evolved over the past decade?

    It certainly more widespread and supported then some "windows only" oleDB technolgoes like ADO. You not find support for ADO on a Apple iPad, but you certainly will find support for ODBC connections. oleDB is a windows only choice. ODBC is a industry standard. So it far more platform neutral.

    If it's so great, why does one have to create these ridiculous "pass-through" queries? 

    Am I missing something here?

    As I pointed out, it only two lines of code:

     CurrentDb.QueryDefs("qPass").SQL = "exec GetLast " & lngIntID
       lngReturnValue = CurrentDb.QueryDefs("qPass").OpenRecordset()(0)
      

    Now one might use a few more lines of code for ease of reading say like this:

       Dim qp      As DAO.Recordset
       Set qp = CurrentDb.QueryDefs("qPass")
       qp.SQL = "exec GetLast " & lngIntID
       lngReturnValue = qp.OpenRecordset()(0)

    Certainly not any more "messy" or less readable then an ADO example that achieves the same result.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    Friday, April 25, 2014 5:18 AM
  • Thanks for your reply Albert.

    I have not kept up with developments in DAO as Microsoft actively encouraged developers to go down the ADO path for many years.  I did and have never looked back.

    I'm encouraged about ACE, which I haven't heard about, so thank you for pointing me in that direction - I will certainly investigate it. 

    I need to figure out a way out of the corner I, and, I think, many of us Access Project developers have painted themselves into, thanks in large part to Microsoft's encouragement.  I still think ADO is a great technology and, had Microsoft persisted in enhancing that technology, it could easily continue to be relevant.  Some of my venting stems from the apparent denigration by Microsoft of Access as a less than serious tool for developers, while creating no viable intermediate path to the .Net platform. (ie: not only has ADO not been enhanced, neither has VBA - it's just dead in the water.  And now they are pushing "macros" in the accdb back end?  Just weird if you ask me).

    And I just don't see .net as a productive platform for a one man development shop, such as myself.  In any case, it's a massive jump from VBA to any .net language.  (I don't think Lightswitch is it either, but time will tell I guess).

    I have a number of happy clients, running highly complex data-centric applications using MS Access Project on the front-end and MS SQL Server on the back end.  A lot of the complex logic lives on the back end.  Performance is great, with some transaction tables numbering in the hundreds of thousands of detailed rows.

    Seems like a daunting task to convert any of them and with dubious short term benefit.  New features in 2013, such as an improved Sharepoint interface, web forms, etc, all sounds good, just a lot of work to just get to the (re)starting gate.

    I guess I'll start with a smallish project and see if a solution for my existing systems becomes more apparent.

    One last question.  You mentioned that ACE has, or will soon have support for stored procedures.  Does this mean that I can pass parameters in a similar fashion as I can in ADO? 

    Incidentally, I don't think that # of lines of code is necessarily that significant, especially if more lines results in more readable and supportable code.  My applications can call stored procedures with any number of parameters passed to (and returned from) them.  If can then inspect the output parameters and continue accordingly in the front-end.  If DAO/ACE requires me to pass this all in a string, then no thanks!  Yes, technically it's one line, but what a mess!

    Best regards,

    Steve

    Friday, April 25, 2014 5:40 AM

  • "ACE continues to receive NEW development dollars while ADO does not. ADO as noted is rather old and is NOT being supported nor adopted by ANY developer community...To state in public that ADO is some kind of good choice right now is simply bad advice."

    Sorry to beat this horse some more, but with all due respect to Albert and his contributions, it is NOT that simple. DAO and JET didn't get updated for years. In fact, they were deprecated in the rest of Windows technology outside of VBA. Did that mean they were on their way out and were a dead-end choice?

    For that matter, VBA itself is derived directly from VB6, (arguably) a "dead" language. Does that mean VBA is on its way out?

    So DAO gets a few new (IMO, mostly pointless) features after years of stasis and a name change, but it still the same old technology with all of the same old limitations.  Are we supposed to be impressed by it now?

    ADP is an Access-specific format, and is on it's way out  (or rather, IS out). The current facts do not suggest, however, that ADO and OLEDB are not "on their way out." 

    For starters, ADO was just recently updated to work with Windows 7. Why would MS bother if it's being deprecated? Second, ADO and OLEDB continue to be on the technological roadmap, and Microsoft has repeatedly cleared up that the SQL Server OLE DB Provider deprecation does not imply deprecation for OLE DB as a whole.

    There has been a mass of confusion about what the SQL Server OLE DB deprecation announcement actually means. It means only that the SQL Server provider/driver that is bundled with newer versions of SQL Server will use the ODBC interface.  ADO is a thin layer for accessing OLE DB, but it's the OLE DB Core Component architecture that it uses, NOT the OLE DB provider (driver). That architecture by design contains a bridge to ODBC, and so you can use an ODBC driver with ADO just by changing the connection string to one that works for that ODBC driver. I've done used it with Oracle and there is little to no difference in performance.

    Unless MS decides to ditch the OLE DB Core Component architecture as a whole, ADO is in no danger of going away. And even if it were, you would have ten years to migrate, far longer than the life of most Access applications anyhow.

    (I suppose a secondary danger would be if the Access development team pointlessly, maliciously blocked the references to ADO. If they did that, I, and a great many other people that work for large corporations would simply not upgrade Access; we would ditch it altogether. I personally would never buy another MS product again.  Some people would also probably just change the names of the .tlb objects and files so that ADO continued to work under a different name and continue using them).

    The fact of the matter is, you can use ADO today in Access the same as you always could, you just have to do it with custom VBA functions, not using the the wizards or ADP. There is little chance MS will ever ditch ADO altogether, because there is no alternative RDBMS-neutral set of commands for the higher-level .COM languages. It's true MS would prefer we all jump ship to .NET anyways, but until or unless they move their own suite of Office programs to .NET, they are not ignorant enough to discontinue ADO. It's used in just too many places where ACEDAO can't be.

    Some would ask: why go to the trouble of ADO in Access to begin with? First of all,  realize that using passthrough SQL syntax is vitally important for database health.  The larger the datasets, the more important it is. And by "database" I mean the RDBMS backend (Oracle, SQL Server, DB2, etc), not Access. JET/ACE syntax might not be that bad against some databases, but against others (like Oracle), it performs horribly. And using it precludes using RDBMS specific features like parallelism that can result in queries running hundreds of times faster than they would otherwise. ADO requires using native/passthrough syntax and so is inherently better-performing against a non-Access database than JET/ACE queries.

    Of course, there are DAO passthroughs that do this also, but passing parameters to PT queries requires creating the query on the fly in VBA, so they're not really easier to implement than using ADO commands and recordsets.  Second (and more significantly) when bound to forms, passthroughs are read-only, which in my book greatly limits their usefulness. Third, they cannot be "disconnected" from the source server like ADO recordsets can. The constant connecting and updating that occurs every time each textbox gets updated on a bound form is huge waste and drain on a network, and on the RDBMS backend. Even if passthroughs would allow you to make updates to a bound form , you would still face this fundamental limitation.  Fourth, bound forms using passthroughs are just inherently slower than using ADO recordsets. I've tried both to compare (with the same SQL Select statement), and the ADO recordsets won every time.

    I've noticed that most of the people who are recommending (ACE)DAO are freelancers that typically build for small-medium size businesses, and the people that prefer ADO work for large companies, and often have apps that have grown much larger than the original scope, or have larger user bases than the typical Access app. I've noticed too that the veteran DBA's (for SQL Server and the others), nearly all prefer ADO to (ACE)DAO/ JET.

    Coincidence? I think not.

    I work on team that that builds Access tools that are deployed to Citrix (remote terminal services). When I  came aboard, everything was JET & Access tables. The servers were bogged down, slow, crippled, practically unusable. Same as what others here have mentioned in their experiences. We moved the larger stuff to SQL Server & Oracle, and used my disconnected ADO recordset approach for those. Problem solved!

    It may not be apparent that there is a difference between using ADO and DAO-bound forms, but if you put enough users on the same machine, you will have some serious problems with ACE and linked tables. I have hundreds of concurrent users in my ADO apps; maybe more than a thousand. With zero performance problems.

    Sure, SQL views help get you partly there, but you will still have a chatty, constantly-connected forms that do not scale well. That chattiness also makes them a poor choice for deploying to desktop across WANs, a limitation the disconnected ADO approach doesn't face. 

    But what about the convenience of being able to join Acess tables to SQL Server, etc?

    Simply put, anyone that regularly uses heterogeneous joins (joins between two different database platforms) for anything but the smallest amount of adhoc data is a clown, and probably has no significant experience querying against large data warehouses.  JET/ACE processes those heterogeneous joins locally; it doesn't allow them to be passed for processing on the RDBMS backend. The resulting memory and CPU problems are often significant, not to mention the fact that it wreaks havoc on the RDBMS query execution plan! This local-processing also tends to occur with the slightest change to the connection string or the DSN even when there are no heterogeneous joins. None of these things are problems with ADO, but they are endemic to JET/ACE queries. Wich is why most dbas and IT professionals hate Access, but even the ones that do are generally tolerant of ADO and ADP.

    Can you get close enough with Linked tables, SQL views, Passthroughs built on the fly, and SQL stored procedures? Sure, you can!  But at the end of the day, to do that right, it isn't going to be a whole lot easier than using a set of reusable ADO functions, and a whole lot less foolproof. There is no real guarantee that JET will play nice and do the right thing with the filters or the joins. The more complicated the application and the join structures, the more vigilant you'll have to be. The more users you have, the more stress on the network or on the terminal server will.  Don't be surprised if it doesn't scale like you want it to and one fine day your shiny new server is hunk of scrap metal.

    ADP users need to be are that you can still use ADO without ADP, and that ADO and OLE DB are not being deprecated. They are still viable choices. At most you should realize that the weaknesses of DAO and JET can be bolstered up with ADO where necessary, even if you don't use a pure ADO approach in Access.  I am going to clean out my class library a bit and then post some examples on how that can be done. I have a busy job, so don't expect it any time soon (probably at least a couple weeks), but I'll put something up as soon as I can, and provide the link on the this thread.

    Some may argue that wth the sort of coding it takes to implement ADO nowadays, .NET is a better choice. I think that depends on the level of your proficiency with a .NET language and your deployment options.  In my estimation, there is still no better RAD tool than Access.

    "What is occurring is the general IT industry and that includes BOTH the .net community, the Access community and the SQL server community ARE ALL EITHER DROPPING support for ADO or moving away from ADO"

    Sorry, but again, this is an oversimplification, and a misrepresentation of the reality.  The .NET community never embraced ADO because from the get-go they had ADO.NET. Which does a lot of the same only easier, and was built to work within .NET specifically. Why should we have ever expected them to use classic ADO?

    Nor is SQL Server dropping support for ADO, because SQL Server never connected to ADO to begin with. They're only dropping support for the OLE DB provider because it's not cross-platform capable, and the difference in performance is negligible. But you can still use the ODBC driver with ADO, so who cares? Not to mention that the SQL Server lineup of products do themselves uses the same underlying OLE DB techology all over the place.

    What is clear, is that the .NET framework evolves constantly, whereas the VBA language is based on "dead" one,  and has been remarkably static over the last 30 years, mostly so that backwards compatibility is preserved.  As such, we should not really care whether there are development dollars in this or that connection technology, because except when new products are added to the lineup, nothing much ever changes anyway. You're more likely to see five more versions of the .NET framework come and go than see the status quo with ADO and OLE DB change.

    And that's just fine by me. Some of us LIKE not having to rewrite our apps every two years to get on board with latest new trend...


    Sunday, June 15, 2014 10:15 PM
  • I much agree that ADO is not going away, but ADP’s are.

    And I do agree there been confusing here. However much of that confusing come from people wanting to use this issue to rant as opposed to nail down some facts based on knowledge.

    I think it begs pointing out that recent investments in VBA are important. There are billions of spreadsheets and office applications that use VBA.

    Once again on this issue it would be unfair to claim or state that VBA is on its way out. The fact that ADO or VBA been remarkable static in features for 15 years does not matter much here.  So your point about VBA being relative static ALSO applies to ADO – it also seen little changes.

    However WHAT DOES matter is new versions are being released.

    Access recently received a new version of VBA and products like VB6 or FoxPro will never see the world of x64 computing like Access. So Access does now have an x64 bit option and support for the windows x64 API. This new VBA is significant since it will allow Access (and ACE) to play with other x64 bit software which we seeing more and more of on the windows desktop.

    And I also stand by my public statement that .net community left ADO.  99% of developers who came from say VB6 or even c++ building windows + database apps were using ADO. And when they adopted .net, they could have continued to use ADO. The problem? Something better and newer came along and they dropped ADO.

    And new .net developers ALSO made that choice to use something newer and better then ADO. The simple issue remains that new and existing .net developers chose something else other then ADO due to them having such a choice.

    I hard pressed to find any developer community adopting ADO these days?

    However I MUCH appreciate the point that since SQL server is dropping oleDB does not necessary mean the end of ADO.  This dropping issue does however much explains the end of ADP’s in regards to Access.

    So my recommending to avoid ADO is mostly in the context of ADP’s not being a good choice right now.

    And given that most Access applications start out with DAO, then even when migrating such products to SQL server then again I recommend keeping DAO code. This advice applies to the VAST majority of Access applications.

    The fact that you are working on some exceptional Access application does not change my “general” advice here. Advice based on “exceptions” or special case applications is not general advice to this community.

    >>but passing parameters to PT queries requires creating the query on the fly in VBA,

    But it only two lines of VBA code. Eg this:

       With CurrentDb.QueryDefs("qryPass")

          .SQL = "exec sp_cust1 " & p1

          .Execute

       End With

    To be fair, we could count above as 4 lines, but I can write the above in two lines. Can you post a more simple solution then ABOVE? In above I did not even have to declare any varibles!

    Can you post a two line solution to call a store proc using ADO and pass a parameter? Again the ADO takes more code and again gives one more billable hours.

    Requiring a pass-through proc for a form is VERY RARE and again NOT general advice. And again "in general" this would result in developers racking up additonal billable hours when not reuiqred.

    The rare and exceptional case is not “good” general advice to the Access community.

    > The constant connecting and updating that occurs every time each textbox gets updated on a bound form is huge waste and drain on a network

    The above is incorrect. Editing data inside textboxes on a form does not cause network traffic.  Traffic ONLY occurs at record load and then at record save time.

    Using disconnected reocrdsets can often cause MORE traffic then this bound setup since two steps occur. The open + pull step, and then the second required re-open + reconnect to write that data out can cause additional network traffic over that of just having the recordset stay open.

    You have this:

     Open connection

      Pull data into record set

      Disconnect

    You now modify that data, you then:

    Re-open

    Push data up

    disconnect

    So you have “two” sets of opens and re-connects here and BOTH steps cost traffic.

    If you leave the form bound, then connection stays open and you save the cost of open/close and re-connecting that recordset. And again this suggests additional cost and more billable hours.

    The supposed advantage of disconnected recordsets to reduce traffic is nullified as opposed to leaving the form datasource open.

    If some application needs disconnected ability such as on a poor WAN, then disconneceted  reocrdsets make sense, but NOT for the reason of saving bandwidth, but that of the connection being more robust. You are confusing saving network bandwidth with that of needing a robust connection.

    If one is building a fully disconnected application and using un-bound forms then Access in 99% of these cases is the wrong developer tool. So once again we are looking for good general advice here and not some super rare exception that few if anyone here will ever require or such advice applies to.

    For existing Access applciatons (DAO) then in vast majority of cases it does not make sense to adopt ADO and again spend the resulting extra billable  hours. You can scale out Access to 100’s of uses with a few basic changes.

    Exactly what % of Access applcations do you think have more then 200 connnected users?

    In closing

    I MUCH am willing to concur for existing ADP applications and migration out of ADP then continuing to use ADO makes MUCH sense. In fact I state in pubic this would be good sound advice.

    Given this topic is ADP’s, then it is most fair to suggest that one can and should continue to use ADO even if migrating to accDB and using linked tables.

    As you point out, as SQL server drops oleDB, ADO should still be viable by using ODBC technology which is what us DAO folks been using all along.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    Monday, June 16, 2014 2:34 AM
  • I much agree that ADO is not going away, but ADP’s are.

    And I do agree there been confusing here. However much of that confusing come from people wanting to use this issue to rant as opposed to nail down some facts based on knowledge.

    I think it begs pointing out that recent investments in VBA are important. There are billions of spreadsheets and office applications that use VBA.

    Once again on this issue it would be unfair to claim or state that VBA is on its way out. The fact that ADO or VBA been remarkable static in features for 15 years does not matter much here.  So your point about VBA being relative static ALSO applies to ADO – it also seen little changes.

    However WHAT DOES matter is new versions are being released.

    Access recently received a new version of VBA and products like VB6 or FoxPro will never see the world of x64 computing like Access. So Access does now have an x64 bit option and support for the windows x64 API. This new VBA is significant since it will allow Access (and ACE) to play with other x64 bit software which we seeing more and more of on the windows desktop.

    And I also stand by my public statement that .net community left ADO.  99% of developers who came from say VB6 or even c++ building windows + database apps were using ADO. And when they adopted .net, they could have continued to use ADO. The problem? Something better and newer came along and they dropped ADO.

    And new .net developers ALSO made that choice to use something newer and better then ADO. The simple issue remains that new and existing .net developers chose something else other then ADO due to them having such a choice.

    I hard pressed to find any developer community adopting ADO these days?

    However I MUCH appreciate the point that since SQL server is dropping oleDB does not necessary mean the end of ADO.  This dropping issue does however much explains the end of ADP’s in regards to Access.

    So my recommending to avoid ADO is mostly in the context of ADP’s not being a good choice right now.

    And given that most Access applications start out with DAO, then even when migrating such products to SQL server then again I recommend keeping DAO code. This advice applies to the VAST majority of Access applications.

    The fact that you are working on some exceptional Access application does not change my “general” advice here. Advice based on “exceptions” or special case applications is not general advice to this community.

    >>but passing parameters to PT queries requires creating the query on the fly in VBA,

    But it only two lines of VBA code. Eg this:

       With CurrentDb.QueryDefs("qryPass")

          .SQL = "exec sp_cust1 " & p1

          .Execute

       End With

    To be fair, we could count above as 4 lines, but I can write the above in two lines. Can you post a more simple solution then ABOVE? In above I did not even have to declare any varibles!

    Can you post a two line solution to call a store proc using ADO and pass a parameter? Again the ADO takes more code and again gives one more billable hours.

    Requiring a pass-through proc for a form is VERY RARE and again NOT general advice. And again "in general" this would result in developers racking up additonal billable hours when not reuiqred.

    The rare and exceptional case is not “good” general advice to the Access community.

    > The constant connecting and updating that occurs every time each textbox gets updated on a bound form is huge waste and drain on a network

    The above is incorrect. Editing data inside textboxes on a form does not cause network traffic.  Traffic ONLY occurs at record load and then at record save time.

    Using disconnected reocrdsets can often cause MORE traffic then this bound setup since two steps occur. The open + pull step, and then the second required re-open + reconnect to write that data out can cause additional network traffic over that of just having the recordset stay open.

    You have this:

     Open connection

      Pull data into record set

      Disconnect

    You now modify that data, you then:

    Re-open

    Push data up

    disconnect

    So you have “two” sets of opens and re-connects here and BOTH steps cost traffic.

    If you leave the form bound, then connection stays open and you save the cost of open/close and re-connecting that recordset. And again this suggests additional cost and more billable hours.

    The supposed advantage of disconnected recordsets to reduce traffic is nullified as opposed to leaving the form datasource open.

    If some application needs disconnected ability such as on a poor WAN, then disconneceted  reocrdsets make sense, but NOT for the reason of saving bandwidth, but that of the connection being more robust. You are confusing saving network bandwidth with that of needing a robust connection.

    If one is building a fully disconnected application and using un-bound forms then Access in 99% of these cases is the wrong developer tool. So once again we are looking for good general advice here and not some super rare exception that few if anyone here will ever require or such advice applies to.

    For existing Access applciatons (DAO) then in vast majority of cases it does not make sense to adopt ADO and again spend the resulting extra billable  hours. You can scale out Access to 100’s of uses with a few basic changes.

    Exactly what % of Access applcations do you think have more then 200 connnected users?

    In closing

    I MUCH am willing to concur for existing ADP applications and migration out of ADP then continuing to use ADO makes MUCH sense. In fact I state in pubic this would be good sound advice.

    Given this topic is ADP’s, then it is most fair to suggest that one can and should continue to use ADO even if migrating to accDB and using linked tables.

    As you point out, as SQL server drops oleDB, ADO should still be viable by using ODBC technology which is what us DAO folks been using all along.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    Don't get me wrong; i'm not suggesting that VBA is on it's way out.  It was an observation posed as a hypothetical question. The point is that stasis is not the same as decline. We're essentially working in an environment that all based on technologies the .NET platform left behind, but which were popular, easy and useful, so retained for the Office suite. So when I hear people talk about the "momentum" behind (ACE)DAO, I can't help but blush.  Let's be real: it's all more or less static, ancient technology.  A few new cosmetic changes don't change the fact that ACEDAO is still instantiated as 'DAO' and is fundamentally the same under the hood as it's always been.

    I agree that continued support (and to the extent that changes necessitate it), new versions are important. But ADO got that with Win7, and MSDASQL has recent changes also the bridge from OLE to ODBC.

    So I'm glad that (I think) we're in agreement that ADO/OLE DB is not going anywhere and nor is VBA. 

    As far as the .NET community abandoning ADO for ADO.NET because it's "better," I think you're missing the point. For one, most corporate shops who work in .NET are constantly upgrading their code to the newest version and utilizing the newest features, and the "use ADO.NET" mantra was preached from day one. .NET'ers frequently buy into the "newer is better" nonsense. Many used LINQ when it came along and now they are with ADO.NET and entity framework. In two years, they'll be changing it up again. Personally, I think they're all crazy for not jumping ship and going to Java, what with all of this planned obsolescence! I'm half kidding here, but stasis of VBA does have its advantages...

    In any case, I think it's only fair to say that ADO.NET isn't "better " than classic ADO in the sense of being faster or more stable; it's simply better integrated with .NET, as it was designed with .NET winforms and their object models in mind. It would be unreasonable, in my mind, to expect .NET developers to use ADO classic indefinitely. It just doesn't have the same ready-made features for easily working with .NET winforms that  ADO.NET does.  That said, there are .NET developers who do still use classic ADO, and not everyone has been pleased with ADO.NET's performance. I know some dbas who have specifically blacklisted it in their organization because of some problems they've had with bind variables.

    Besides, as Access developers, our whole platform is non-.NET, so I see no reason why we should  care whether .NET embraces ADO. They certainly aren't embracing (ACE)DAO either.

    I cannot agree to your assertion that using passthrough syntax on a form is bad advice, or an exception to the rule. It is always best performance-wise to use SQL that is "native" to the RDBM back end. It's more efficient, and rarely much more difficult to implement.

    Now, I will concede that, if the user can create SQL views encompassing all of the tables in the query,  then the benefits of passthrough syntax become pretty marginal. But not everyone has the privileges to create SQL views and stored procedures. It tends to vary a lot by platform and by role even in the same organization (I can't reference production tables in Oracle, but I can in SQL, for instance).  And the important distinction is that the DAO-passthroughs don't allow for direct updates to the underlying bound tables, but ADO-bound forms do.

    Of course, it's perfectly possible to issue a DAO passthrough update that accomplishes the same thing; that just means more work. In the end, I doubt the amount of coding required with that is much different from using ADO. And to be truthful, it doesn't really matter whether it's two lines or four IF one keeps a class of reusable functions. I have that, so for me ADO is quite viable for me.

    As far as the disconnected question goes: I'm aware that disconnecting entails reconnecting at the end, but the second and subsequent connections are more or less instantanous anyhow. It's not esatabilishing a connection that's causing the overhead though: it's making the updates. For bound forms, this is a constant process. Data is passed across the wire every time the form becomes "dirty", and hard drive writes are made to the database server the entire time the time the form is opened as fields are keyed in. Sometimes that may be a wash, but what if the user has the form open a half hour or hour, filling out each of the fields? Each of those updates would mean data passed across the network, and updates made to the databse server, instead of just one at the end to capture it all.  The bottom line is: if the disconnected approach was inefficient, MS wouldn't have made it the default connectivity approach with ADO.NET. 

    Many Access developers work in niche areas of large companies with Wide Area Networks. I seriously doubt that is  "some super rare exception that few if anyone here will ever require or such advice applies to." I do agree that Access is not tthe easiest tool in which to implement disconnected approach and that a robust LAN connection with little other traffic largely obviates the need. But few of us are lucky enough to be working in that kind of a set up. For many of us, the choices are: a) desktop deployment over WAN or b) deployment to Citrix/Remote Terminal Services. The chattiness of bound forms rules out a) and the CPU and RAM usage of ACE/JET makes b) problematic when there are very many users. Often that doesn't mean 200+ users of one Access tool, but rather, 50 of four or five different ones.

    In the end, I merely wish to point out that ADO is still a viable option insde .ACCDB's (although without the reusable functions, it's pretty tough to implement), and that ADP users should be aware of this.  I think we're in agreement there since you acknowledge that it's  "fair to suggest that one can and should continue to use ADO even if migrating to accDB and using linked tables...ADO should still be viable by using ODBC technology." I will post some things in due time that make that implementation a lot easier, and in fact remove the need for linked tables altogether (if one so chooses).

    Albert, in closing, I wish to thank you for you knowledge and contributions to the Access community, even if we don't see perfectly eye-to-eye on this on a couple of points. I suspect the difference in environment at least partly explains that, and I hope this hasn't come across as a rant.

    Monday, June 16, 2014 5:21 AM
  • Your post and comments are very well thought out and  you made rather good points and use cases here. And I also think that if the developer(s) have great ADO experience, then again using ADO makes sense for scaling out with SQL server.

    In fact, given good experience with SQL server and ADO I am much willing to bet most experienced developers can well grasp and see those tipping points in which using ADO makes sense – it simply not an all or nothing choice either way here.

    And I do think I have some fault in not making the best distinction between ADP’s going away and that of ADO still being a viable choice.

    I gracefully accept you made a great case for ADO and have done everyone a favor here by clearing up that ADO remains a viable choice for Access developers.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Monday, June 16, 2014 4:13 PM
  • Your post and comments are very well thought out and  you made rather good points and use cases here. And I also think that if the developer(s) have great ADO experience, then again using ADO makes sense for scaling out with SQL server.

    In fact, given good experience with SQL server and ADO I am much willing to bet most experienced developers can well grasp and see those tipping points in which using ADO makes sense – it simply not an all or nothing choice either way here.

    And I do think I have some fault in not making the best distinction between ADP’s going away and that of ADO still being a viable choice.

    I gracefully accept you made a great case for ADO and have done everyone a favor here by clearing up that ADO remains a viable choice for Access developers.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thanks for the kind words, Albert. :) That means alot to me coming from a man of your knowledge and distinction. For my part, I have to agree that in many, many cases, linked tables and DAO do the job just fine. It is all very much dependant on the specifics of the environment, the structure of the application, and the implementation. As you say, it's "simply not an all or nothing choice either way here."

    I do hope the Access developer team takes note  that there are developers like myself who still regularly use ADO in Access. So long as the OLEDB core architecture is still being suppported at large, I hope they will not make any efforts to stamp out ADO. I see no reason why they would do something so malicious as say, block the reference (and such a thing would be an exercise in futility anyhow), but I wanted to make my voice heard here all the same. As you noted before, part of the problem with ADP was that few people in the community were vocal in their support of it until it was too late.  In my mind, it's best to have as many tools in the toolbox as possible. ADO is still quite an effective instrument, so let's all keep that in perspective and relish the fact that we have it as an option, whether it's our particular brand of cocoa or not.

    Monday, June 16, 2014 4:39 PM
  • What are the differences, advantages, disadvantages of ADO over pass-thru queries ?

    I'm especially interested in the case of calling stored procedures.

    Monday, June 16, 2014 6:22 PM
  • I will do my best to answer the ADO part as I have not used a pass-thru query since "MS Access Project" first became available. 

    Let's start with a trivial sample stored procedure:

    CREATE PROCEDURE prcCalcSomething
        @ProductId    int,
        @Result        int        OUTPUT,
        @strMessage    varchar(200)    OUTPUT
    AS
    
    SELECt    @strMessage = ''
    
    /*
    Write your fast, efficient SQL code here.
    Lightning fast performance
    Calculations are done on the back-end server where they belong
    Using BEGIN ... TRY in Procedure, you can capture errors and return them to the front end
    Trivial example below higly calculation intensive, and comlex, multi-table, high volume transactions
    */
    
    BEGIN TRY
        SELECT    @Result = SUM(Qty)
        FROM    tblSomeTable
        WHERE    ProductId = @ProductId
    END TRY
    
    BEGIN CATCH
        SELECT @strMessage = CAST(ERROR_NUMBER() AS varchar(10)) + ' - ' + ERROR_MESSAGE()
    END CATCH

    Now, let's have a look at how this would be called from your VBA code:

    Sub CalcSomething(intProductId As Long)
    
        Dim cn As New ADODB.Connection, cmd As ADODB.Command, prm As ADODB.Parameter, rs As New ADODB.Recordset
        
        cn.Open GetCnn()
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = GetCnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "prcCalcSomething"    ' CommandText tells ADO the name of the stored procedure to execute
        
        Set prm = cmd.CreateParameter("ProductId", adInteger, adParamInput, , Null)     '<- this is the input argument (no practical limit on number of arguments.
        cmd.Parameters.Append prm                                                       '<- Add the parameter to the queue
        Set prm = cmd.CreateParameter("Result", adInteger, adParamOutput, , Null)       '<- first OUTPUT argument
        cmd.Parameters.Append prm
        Set prm = cmd.CreateParameter("strMessage", adVarChar, adParamOutput, 200, 0)   '<- second OUTPUT argument to trap any errors reported by the stored procedure
        cmd.Parameters.Append prm
        cmd.Execute , , adCmdStoredProc <- execute the stored procedure
        
        If Len(Trim(cmd.Parameters("strMessage"))) > 0 Then
            MsgBox "Error! Do something here", vbExclamation
            Exit Sub ' should be a more elegant error handling method here!
        End If
        
        intResult = cmd.Parameters("Result")
        set cmd=nothing <- close command object
    End Sub
    What I really like about ADO is:

    a) The code is highly readable in that the parameters are passed to the stored procedure in an orderly, well documented fasion.

    b) There is a clean mechanism for inspecting multiple output parameters so your front-end code can decide what to do after executing the stored procedure.

    I'm sure ADO pass-through queries could accomplish this, but, as I recall, the text string required to build the pass-through query is not very pretty and I'm not aware of the ability to inspect individual parameters after execution.  Probably would be hard to debug.  Probably not a big issue with a small number of parameters, but I have some stored procedures that require 10 or more parameters to be passed to the stored procedure on the back end - I like how cleanly ADO handles it.  More "lines of code"? probably.  More readable?  Your call.

    I suspect performance is better as well, but I haven't tested against ADO for years.

    The thing I will miss that MS Access 2013 does not support is the ability to use stored procedures as record sources for forms.  I do this for ALL forms that are of the "Continuous form" variant (ie: multi-row, grid type forms".  In ADP front ends, the form has the "Input Parameters" property, which allows you to pass any number of input parameters to the stored procedure.  It's a bit clunky, and I was hoping for some improvements here in the 2013 version, but they dropped ADP's entirely, much to my dismay.

    Hope my comments are helpful

    Steve



    • Edited by Stephen7 Monday, June 16, 2014 7:05 PM
    Monday, June 16, 2014 7:00 PM
  • I think just for calling SQL Server SP's, not much. 

    Basically, if I had to sum it up, ADO is a general-purpose set of commands for working with relational and non-relational databases using little memory and keeps the processing server-side, same as it's OLE DB parent. It has always been a general-purpose set of commands, not specific to Access, and focused on low memory consumption and speed. (ACE) DAO, on the other hand, is a set of commands for using the ACE(JET) provider inside Acess, and is geared more towards easy use with local Access tables, ODBC, and heterogeneous joins. There is a lot overlap in functionality, though. One might sum it by saying that the one is a general purpose set of commands that assumes working in native SQL;  the other a detailed window into Access and better integrated with Access as a whole.

    It's perfectly possible to mix it up with a little of both in the same project, and most experienced Access devs do that, to one degree or another. For the express purpose of calling SP's though, I think you can use either, and it will not matter mach.

    Mainly where i use ADO is with the disconnected forms and working with the recordset object itself. I have a loop function that spools data to .csv files. I can backup an 15 million row table in 10 minutes. The memory consumption never climbs even as high as the average webpage consumes.  Another one that transfers from one database platform to another, without actually touching access objects or tables, that runs faster than most SSIS transfers to flat file.

    I think if you have SP creation and execute privileges, you could use either, without much difference, as the processing therein will be server-side regardless.  I assume you have  the ability to create views also. If so, that will go along way.

    Honestly, it can really come down to the platform. JET/ACE plays pretty nicely with SQL Server, ok with DB2,  but I wouldn't use it to connect with Oracle in a million years.

    It's kinda one of those things where you just have to try a couple different approaches and decide what works best for you and your environment. 

    Monday, June 16, 2014 7:11 PM
  • I will do my best to answer the ADO part as I have not used a pass-thru query since "MS Access Project" first became available. 

    Let's start with a trivial sample stored procedure:

    CREATE PROCEDURE prcCalcSomething
        @ProductId    int,
        @Result        int        OUTPUT,
        @strMessage    varchar(200)    OUTPUT
    AS
    
    SELECt    @strMessage = ''
    
    /*
    Write your fast, efficient SQL code here.
    Lightning fast performance
    Calculations are done on the back-end server where they belong
    Using BEGIN ... TRY in Procedure, you can capture errors and return them to the front end
    Trivial example below higly calculation intensive, and comlex, multi-table, high volume transactions
    */
    
    BEGIN TRY
        SELECT    @Result = SUM(Qty)
        FROM    tblSomeTable
        WHERE    ProductId = @ProductId
    END TRY
    
    BEGIN CATCH
        SELECT @strMessage = CAST(ERROR_NUMBER() AS varchar(10)) + ' - ' + ERROR_MESSAGE()
    END CATCH

    Now, let's have a look at how this would be called from your VBA code:

    Sub CalcSomething(intProductId As Long)
    
        Dim cn As New ADODB.Connection, cmd As ADODB.Command, prm As ADODB.Parameter, rs As New ADODB.Recordset
        
        cn.Open GetCnn()
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = GetCnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "prcCalcSomething"    ' CommandText tells ADO the name of the stored procedure to execute
        
        Set prm = cmd.CreateParameter("ProductId", adInteger, adParamInput, , Null)     '<- this is the input argument (no practical limit on number of arguments.
        cmd.Parameters.Append prm                                                       '<- Add the parameter to the queue
        Set prm = cmd.CreateParameter("Result", adInteger, adParamOutput, , Null)       '<- first OUTPUT argument
        cmd.Parameters.Append prm
        Set prm = cmd.CreateParameter("strMessage", adVarChar, adParamOutput, 200, 0)   '<- second OUTPUT argument to trap any errors reported by the stored procedure
        cmd.Parameters.Append prm
        cmd.Execute , , adCmdStoredProc <- execute the stored procedure
        
        If Len(Trim(cmd.Parameters("strMessage"))) > 0 Then
            MsgBox "Error! Do something here", vbExclamation
            Exit Sub ' should be a more elegant error handling method here!
        End If
        
        intResult = cmd.Parameters("Result")
        set cmd=nothing <- close command object
    End Sub
    What I really like about ADO is:

    a) The code is highly readable in that the parameters are passed to the stored procedure in an orderly, well documented fasion.

    b) There is a clean mechanism for inspecting multiple output parameters so your front-end code can decide what to do after executing the stored procedure.

    I'm sure ADO pass-through queries could accomplish this, but, as I recall, the text string required to build the pass-through query is not very pretty and I'm not aware of the ability to inspect individual parameters after execution.  Probably would be hard to debug.  Probably not a big issue with a small number of parameters, but I have some stored procedures that require 10 or more parameters to be passed to the stored procedure on the back end - I like how cleanly ADO handles it.  More "lines of code"? probably.  More readable?  Your call.

    I suspect performance is better as well, but I haven't tested against ADO for years.

    The thing I will miss that MS Access 2013 does not support is the ability to use stored procedures as record sources for forms.  I do this for ALL forms that are of the "Continuous form" variant (ie: multi-row, grid type forms".  In ADP front ends, the form has the "Input Parameters" property, which allows you to pass any number of input parameters to the stored procedure.  It's a bit clunky, and I was hoping for some improvements here in the 2013 version, but they dropped ADP's entirely, much to my dismay.

    Hope my comments are helpful

    Steve



    Good point about the return parameters, Steve. I think if you have a multiple return values in the SP, you could still get at in DAO from the the return query by using the DAO recordset object and looking at the column position, extracting it  I assume from a string. Not as pretty or clean as just looking at the return values of the parameters in my book, but would still do the job. It That would be two steps backwards and one forward to have to go that route in my book though.

    What would really be nice is if MS learned the lessons of why Access devs sometimes reach towards ADO  in the first place and updatated the ACEDAO object model to use the best of both worlds. Same as with the OLEDB --> ODBC about-face they're engated in.

         :Hello....hello....helloo. Is there anybo-dy IN there?..... :

    Monday, June 16, 2014 7:37 PM
  • Hi Aaron,

    Agree totally with your last comment!  Fingers crossed for some major improvements in the next version...

    Monday, June 16, 2014 7:56 PM
  • Honestly, it can really come down to the platform. JET/ACE plays pretty nicely with SQL Server, ok with DB2,  but I wouldn't use it to connect with Oracle in a million years.

    I'd love to know what makes Oracle so "different" ?

    Is it a client-side driver built into ACE ? Can a different driver be substituted ?

    Monday, June 16, 2014 7:58 PM
  • Honestly, it can really come down to the platform. JET/ACE plays pretty nicely with SQL Server, ok with DB2,  but I wouldn't use it to connect with Oracle in a million years.

    I'd love to know what makes Oracle so "different" ?

    Is it a client-side driver built into ACE ? Can a different driver be substituted ?

    We could start with the fact that it's owned by a different company; is a different RDBMS with different SQL sytnax (quite farther removed from ACE/JET's than SQL Server is); and that it's helmsman has no love for anything Microsoft (and so is extremely unlikely to make strides toward greater compatability on any front).

    And then of course, with Oracle, by means of it's exhaustive catalog of Optimizer Hints,  you have more control over the execution strategy than any other database--so more that can go wrong without explicit control over the execution. Greater scalabality, you might say, but more potential things that can go awry using generic SQL. To truly get good peformance, native syntax is essential, and an understanding of the db engine's quirks and options. This all makes JET/ACE a very poor fit by any stretch. 

    It may seem academic, but what until you deal with source tables that in the hundreds of millions or billions of rows.. But of course, don't take my word for it: look on Oracle's website or ask any experienced Oracle DBA that works with a datawarehouse of hundreds of millions or billions of rows on each table, and ask them their opinion on ACE/JET.

    I won't say that using passthrough's can't hack it, but I and others have tried it, and it is not quit the same. For one, you have to keep flipping the returns rows property around. Second, better get that connection string just right and use a login form to pass the credentials, then delete the query immediately afterwards, or risk advertising your PW to the whole world. Third, you'll still need a JET/ Query to paste the results into an Access table, or pass between DAO recordsets. Do that....but watch out for CPU problems!

    In my experience: things go a lot smoother passing the values back and forth among an ADO recordset on the source and a local DAO for the return values, or just keeping it all server-side with ADO recordsets and commands.

    If PT's are your preferred poison, you'll need to create a separate PT query for each transaction. Oracle IDE's delineate transactions semi-colon, but you can't actually use the semi-colon (in either ADO or DAO), any place but the end without triggering an 'invalid character' runtime error.  That sort of limitations makes an ADO command object much more attractive than having to create an actual query object for each transaction; set the connection string and the return records property; make sure the DSN isn't read-only; run the query; and then dispose of the query aftewards.  ADO commands are pretty well-suited towards the task, but in DAO, it becomes something of a chore, with questionable performance implications.

    Incidentally, this tends to be the breakdown with most of this DAO/ ADO stuff: the further you delve into the server side of things for performance, the more you appreciate ADO. The more you distance yourself from Microsoft's products and become immersed in other pools of big data, the less suitable JET and (ACE) DAO seem suited towards the task.  It is no concidence that you find the biggest proponents of JET and Access among the big database admins, but they generally have a tolerant or even favorable view of classic ADO.

    But again: the devil's all in the details. Most of what one can do in one, can be done in the other, by hook or by crook, and no one size fits all.

    Options are good. Let's all quit talking about it like it's a  football match and enjoy that we have thest things, options.

    Cheers,

    Aaron

    Monday, June 16, 2014 9:11 PM
  • Wow, Aaron, you are quite the uber guru on this subject.

    Thanks so much for your insights.

    Tuesday, June 17, 2014 12:24 AM
  • Wow, Aaron, you are quite the uber guru on this subject.

    Thanks so much for your insights.


    My pleasure S.W. Just my observations, subjective of course. I always say: try different things, find the best thing that works for you in your environment. It's fun to learn new approaches and what works best can sometimes be surprising.
    Tuesday, June 17, 2014 5:02 AM
  • But how is that LONG post of code better or more readable then then this:

       With CurrentDb.TableDefs("MyPass")
    
         .SQL = "exec MySproc" & ProducutID
    
         intResult = .OpenRecordset()("Result")
    
       End If
    

    What is this fascination with having to write whacks of code that fill up a whole screen? (perhaps creating lots of billable hours?).

    The above is readable, and certainly far easier to write.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Wednesday, June 18, 2014 9:04 PM
  • Your skill at backhanded, condescending insults no longer surprise me Albert.

    So all of the stored procedures that you write have only one argument?  Perhaps you can show me what your code looks like with 20 parameters?

    Wednesday, June 18, 2014 9:09 PM
  • So all of the stored procedures that you write have only one argument?  Perhaps you can show me what your code looks like with 20 parameters?

    A couple of questions:

    Do DAO pass-thrus require single quotes around string parameters ?

    Are named parameters supported in the EXEC statement ?

    Thursday, June 19, 2014 12:11 AM
  • Access Data Project ADP in Access 2013 is called ADP+, please look at this link for more information:

    http://www.joakimdalby.dk/HTM/ADPX.htm


    Monday, September 08, 2014 1:43 PM
  • Access Data Project ADP in access 2013

    who share adpx.accde?

    Friday, September 12, 2014 7:10 AM
  • Hi Weichen, you are welcome to contact me, see the homepage at the buttom of the article in above link, to have a free test version of ADPX.accde running in year 2014 :)
    Saturday, September 13, 2014 8:23 AM
  • Signing in just to upvote this.  Open source people never cease to amaze me -- no matter what problems MS creates (then refuses to admit), there will be prodigies like you to work around and get it working right again. :)
    Friday, September 19, 2014 11:46 PM
  • Thank you very much sidjmt, yeah it is fun to extend good old Access and bring back adp in Access 2013 by ADP+. GodBless :)
    Wednesday, September 24, 2014 7:37 PM