Solved

Converting an Access adp to an Access accdb

Posted on 2016-08-11
13
24 Views
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?
0
Comment
Question by:alevin16
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points (awarded by participants)
Comment Utility
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.
2
 

Author Comment

by:alevin16
Comment Utility
Hello Scott

I will give that a shot.

Thank yoU!
0
 
LVL 12

Expert Comment

by:funwithdotnet
Comment Utility
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!
1
 

Author Comment

by:alevin16
Comment Utility
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
Andy
0
 
LVL 84
Comment Utility
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.
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 125 total points (awarded by participants)
Comment Utility
Here are Microsoft's official recommendations:
https://msdn.microsoft.com/en-us/library/office/jj618413.aspx

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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points (awarded by participants)
Comment Utility
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?
0
 
LVL 84
Comment Utility
Just because data is in a SQL Server database, doesn't mean it is in the cloud.

The author said they're using Azure.
0
 

Author Comment

by:alevin16
Comment Utility
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?
First-Error.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
@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.
0
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:alevin16
Comment Utility
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.
0
 
LVL 84
Comment Utility
The methods suggested in my comment are one method you can use to convert. Other suggestions for different platforms are certainly valid as well.
0

Featured Post

Free book by J.Peter Bruzzese, Microsoft MVP

Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s free!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now