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^rd 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?

• IronSpeed Designer http://www.ironspeed.com/products/SharePoint-Application-Development.aspx
• RADvolution http://www.developguidance.com/RADvolution_features.htm
• DevExpress http://www.devexpress.com/Index.xml

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

Answer 1

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

Answer 2

>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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

 

Answer 7

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

[e-mail]

Answer 8

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

Answer 9

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

Answer 10

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

 

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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.

Answer 16

Hi s_solt

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

"s_solt" schrieb im Newsbeitrag news:[e-mail]...

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

Answer 17

Hi Berry

"BerryVolley" schrieb im Newsbeitrag news:[e-mail]...

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

Answer 18

Hi Berry

"BerryVolley" schrieb im Newsbeitrag news:[e-mail]...

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

Answer 19

Hi Heimdal

"Heimdal Data AS" schrieb im Newsbeitrag news:[e-mail]...

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

Answer 20

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!

Answer 21

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.  

Answer 22

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

Answer 23

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?  

Answer 24

>>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

Answer 25

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."  

Answer 26

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

Answer 27

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!  

Answer 28

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

Answer 29

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

Answer 30

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?

Answer 31

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

Answer 32

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.

Answer 33

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.

Answer 34

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.