Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Converting an Access adp to an Access accdb

Posted on 2016-08-11
Medium Priority
Last Modified: 2016-09-05
Hello All

Is there a reliable way to convert an Access adp to an accdb?  As most of you know :D  I have an Access program that has an SQL backend up on Azure, and a front end that is loaded with VBA.

It was created in Access 2010 and Access 2013 and greater do not support adp.  

I am dreading doing this because I cannot believe there is an easy or reliable way (I do not want to have to worry about recoding the VBA)

Any advice?
Question by:alevin16
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points (awarded by participants)
ID: 41752520
I don't know that you'd have to recode, but to me the best method would be:

1. Create a new, blank Access database
2. Create links to your SQL Tables in that new database
3. Import everything from your old .adp file to the new .accdb file, except for tables

Some of the Queries in your adp file may actually be Views on the server. If so, you would link them as if they were Tables.

Author Comment

ID: 41752545
Hello Scott

I will give that a shot.

Thank yoU!
LVL 12

Expert Comment

ID: 41752611
I think Mr. McDaniel's advice is exactly correct.

FWIW, I get the impression that it might be a good time to plan a migration to something more sustainable while you have time. The great news is you already have the data in the cloud.

Good luck!
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 41752645
Hello funwithdotnet

Do you have any suggestions for another platform?  I would love to move it to something else so any advice is greatly appreciated!

Thank you
LVL 85
ID: 41752663
Given his handle, I'm sure he would agree that a .NET environment would be more suitable for your needs :)

FWIW, I agree that planning a move to .NET is not a bad idea, but this is something that can take quite a bit of effort (and money, if you can't do it yourself). The .NET platform gives you much greater latitude in terms of design and such, but it's definitely not Access, and there is a fairly steep learning curve.
LVL 12

Assisted Solution

funwithdotnet earned 500 total points (awarded by participants)
ID: 41752718
Here are Microsoft's official recommendations:

I guess the closest migration not involving Access would be to a VB.NET WinForms app. The data source stays the same (for any .NET solution) and the designer & code are about as close as it gets. Plus you can manage the database in Visual Studio if you like.

I've done several migrations from Access like that. The problem is that it's difficult to re-use anything. Each project was basically a new app to replace & extend the functionality in the old app. In all those projects, there was only one bit I wasn't able to replicate. It was not some Access feature, it was a Acrobat drag-n-drop feature from pre-Windows UAC days.

Since each project necessarily received new architecture, engineering and development, any of them could have been done a number of different ways. I'd say whatever has a probable ten-year lifespan, is sufficiently supported, can use your data, and is convenient & most economical for you is the way to go.
LVL 39

Assisted Solution

PatHartman earned 500 total points (awarded by participants)
ID: 41752768
Just because data is in a SQL Server database, doesn't mean it is in the cloud.

Regarding Access Web apps - don't go there.  They are completely dependent on SharePoint and do not support a programming language which makes them very limited in capability.

There is no path from an Access app to any other platform.  The best you can do is to move the data.  Everything else is a complete rewrite so unless you have the correct skill set, you'll need to hire someone to do it for you and you can figure the cost to be 4 to 10 times the cost of developing the original application even though they are starting with a working prototype.  Access is a RAD tool (Rapid Application Development).  The .net options are not and are far more labor intensive as development platforms.

It is obvious that funwithdotnet doesn't think much of Access.  Access is what it is.  It doesn't pretend to be anything else (except for the failed attempts at Web apps).  You would never use Access to develop a graphics intensive game or a TSR program or a wiget.  Access shines when you are developing a data-centric client/server application.  That's what it does and nothing on the market does it better.  I'm OK with my pen not being a phone and my car not being a plane.  Why do people pan Access because it is a targeted tool?
LVL 85
ID: 41752855
Just because data is in a SQL Server database, doesn't mean it is in the cloud.

The author said they're using Azure.

Author Comment

ID: 41752871
Hello Everyone

I love reading all these different views and information.  To go back a little, I created a new blank database and linked the tables and views.  

I then exported the Forms, Reports, Macros, and Modules from the adp to the new accdb (that was such fun)

I tried running the new non-adp and I got this error (I have attached it) in one of the modules.  I am not sure why it is not recognizing it, like I said I copied everything.

ANy ideas?
LVL 39

Expert Comment

ID: 41752887
@Scott, We use Azure also but the database isn't in the cloud.

@alevin16, Try setting a reference to the ADO library.  It isn't set by default.
LVL 85
ID: 41759141
And after making that reference, you should Compile the application. This should tell you if other references are missing.

To compile, first make a backup and then open the VBA Editor and click Debug - Compile. Fix any errors that you can, and post back here with those that you cannot fix.

Author Comment

ID: 41759292
Sorry for the delay in writing back.  We have had other issues happening (thankfully not related to this).  I am going to be doing more work and hopefully have some more success.
LVL 85
ID: 41784449
The methods suggested in my comment are one method you can use to convert. Other suggestions for different platforms are certainly valid as well.

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Feb. 28, Amazon’s Simple Storage Service (S3) went down after an employee issued the wrong command during a debugging exercise. Among those affected were big names like Netflix, Spotify and Expedia.
Microsoft Office 365 is a subscriptions based service which includes services like Exchange Online and Skype for business Online. These services integrate with Microsoft's online version of Active Directory called Azure Active Directory.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question