Access 2013 apd alternative

[Andrew Barker]

No doubt there are other similar threads. I know that adps have been deprecated from Access 2013. And i have seen the microsoft advice for the various alternatives:
upgrade to 2013, and convert adp to accdb linked to sqlserver tables
upgrade to 2013, and move client to .NET
stay on 2010, and dont upgrade back end past SQL2012
My question is have any of you taken any of these options, and what are your experiences, gotchas etc, as we have to make a decision to go one way or another with our local government in-house databases which are currently access 2002 adp front ends on SQL2008.

 

[Pat Hartman]

Since you already have an adp, you will need to convert it to move on. I never went the adp route because I wasn't willing to work with an "Access" app that couldn't use "Access" tables. Not to mention the fact that they couldn't link to anything other than SQL Server and were completely dependent on SQL Server version. I have never attempted to convert an adp so I'm not sure what is involved but I'm going to guess that much of the code you have will no longer be needed but there is no conversion route that I am aware of. If the adp supports export to text, you might compare the exported file for a form to what you get when you export a form from a standard .mdb or .accdb. You might be able to export your objects and fix up the text files and then import them into an .accdb. You will have to convert all your queries also. I would also export them as text using SSMA and go global changes to modify the syntax before importing them back into Access.

If you want to develop an app, go with linked tables using A2010 or A2013 and you can use whatever version of SQL Server you want or horror of horrors link to Oracle and ACE tables also.

 

[Andrew Barker]

Thanks for your advice Pat. To clarify:
For performance and scalability, we are keeping the back end tables in SQL server. Not to mention all the procedures and functions in the back end which we dont want to re-write.
So really its a matter of deciding what to do with the adp client. With the arrival of access 2013, it would be great to hear from those who have already decided what to do with their adp front ends and why.....any pains/success stories?

 

[Pat Hartman]

I assumed you would be keeping the SQL Server BE. If you are not going with bound forms, it makes no difference what your FE is. All roads lead to mucho programming. So, your choice should depend on what you can support and how you want to distribute the FE.

A2013 isn't going to help you there. If you go for a web app, not only is it a complete rewrite but you may not actually be able to recreate your app since you will be limited to macros only. MS didn't trust us with a real programming language. So before you embark on this course, make absolutely sure you can provide the functionality you need. For example - reports!!!

Almost all of my apps have non-Jet/ACE BE's. In all cases, they use bound forms and because they were designed with client/server in mind, their performance is fine. In a couple of cases I created views for convenience and in others I created stored procedures to handle extensive batch processing. For the interactive FE - no pass-throughs (except for batch deletes) and no stored procedures have been required. Some of the apps have tables with over half a million rows.