Solved

Converting an Access adp to an Access accdb

Posted on 2016-08-11
13
27 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)
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.
2
 

Author Comment

by:alevin16
ID: 41752545
Hello Scott

I will give that a shot.

Thank yoU!
0
 
LVL 12

Expert Comment

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

Author Comment

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

Assisted Solution

by:funwithdotnet
funwithdotnet earned 125 total points (awarded by participants)
ID: 41752718
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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?
0
 
LVL 84
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.
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:alevin16
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.
0
 
LVL 84
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.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Synchronize a new Active Directory domain with an existing Office 365 tenant
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

863 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

28 Experts available now in Live!

Get 1:1 Help Now