Migrating MS Access Data Projects (.adp's) to Access 2013 mdb's, what's best practice?
Hi experts,
I'm a seasoned developer in MS Access, and use VBA extensively when processing jobs. I'm really only looking for 'best practice' advice from my comrades here.
As you may be aware, MS has essentially pulled the rug out from under us Access Data Project (.adp) developers by no longer supporting (deprecating) it in their Office 2013 offering.
I've already switched several of my smaller .adp apps over to the ODBC format, but have one relatively HUGE program to migrate over from .adp to .mdb. This program has around 4500 lines of VBA code that's processing 100's of business rules, and is collectively pulling from ~2 dozen data sources, including tables, views, and sorted procs, some of which connect via Linked Servers.
My best guess at this point is to replicate this complex app as an .mdb, and attempt trying to connect all connections to it via ODBC, and possibly also/alternatively using pass-through queries.
Experts: guess what I'm asking here is this: given the above, what would you consider 'best practice' towards migrating the app from .adp to .mdb?
Thanks, in advance,
Dave Rowland
I'm a seasoned developer in MS Access, and use VBA extensively when processing jobs. I'm really only looking for 'best practice' advice from my comrades here.
As you may be aware, MS has essentially pulled the rug out from under us Access Data Project (.adp) developers by no longer supporting (deprecating) it in their Office 2013 offering.
I've already switched several of my smaller .adp apps over to the ODBC format, but have one relatively HUGE program to migrate over from .adp to .mdb. This program has around 4500 lines of VBA code that's processing 100's of business rules, and is collectively pulling from ~2 dozen data sources, including tables, views, and sorted procs, some of which connect via Linked Servers.
My best guess at this point is to replicate this complex app as an .mdb, and attempt trying to connect all connections to it via ODBC, and possibly also/alternatively using pass-through queries.
Experts: guess what I'm asking here is this: given the above, what would you consider 'best practice' towards migrating the app from .adp to .mdb?
Thanks, in advance,
Dave Rowland
I would recommend converting to .ade so as to protect your VBA. IF you decide to upgrade to 07 and higher format, then that would be the .accde equivalent. Also, from my experience, the .ade/accde has less affinity to get corrupted.
Sorry smarturtle, thanks for replying, but your answer is totally unrelated to my issue. I.e. I always convert this app to .ade before offering it to end users. If you'll carefully re-read my question, you'll likely better understand it.
As you already have done some convertings, now facing a huge job, it sounds like you are reaching out for a straw - but I haven't heard of any other method than hard work and just start working through it.
You may be able to simulate some of your present functionality via pass-through queries as you are already aware of.
You could chose to use ADO, but that is as much a dead end as ADP.
/gustav
You may be able to simulate some of your present functionality via pass-through queries as you are already aware of.
You could chose to use ADO, but that is as much a dead end as ADP.
/gustav
I doubt it is as simple as converting to linked tables. I think all the objects are different so you might have to open forms and reports in design view and copy the controls and paste on to forms in an .accdb and then copy the code. I only have A2013 loaded so I don't know if there is a save as option to convert to an .mdb.
As I recall, ADP's required a huge investment in code compared to .mdb's but you might be able to use disconnected recordsets with ADO and salvage the code.
As I recall, ADP's required a huge investment in code compared to .mdb's but you might be able to use disconnected recordsets with ADO and salvage the code.
Hi Gustav, thanks for the reply, but (ideally) I'm hoping to hear from a fellow developer who's actually "been-there, done-that" when it comes to migrating from sizable .adp to .mdb apps like mine.
Must say, however, I love the RAD/IDE environment that MS Access offers, it hasn't failed me (nor more importantly, my end-users) whatsoever in the past 18 years I've been using it. Oh yea, there have been obstacles for sure, but got over them all.
OMG, my VB code is all ADO, is that going away too!??? If so, this is the first I've heard of it. Please say it ain't so! : (
Must say, however, I love the RAD/IDE environment that MS Access offers, it hasn't failed me (nor more importantly, my end-users) whatsoever in the past 18 years I've been using it. Oh yea, there have been obstacles for sure, but got over them all.
OMG, my VB code is all ADO, is that going away too!??? If so, this is the first I've heard of it. Please say it ain't so! : (
Hi PatHartman, thanks for your reply. I already know that ALL of the form/report/VBA Objects can be copied-over to a new, blank Access DB via simple drag-n-drop. That's sweet, and expect it'll be my starting point.
Just as an FYI: from my experience, there is zero difference in VBA coding when developing as mdb vs adp, it's just very cool and functional to do so! : )
Just as an FYI: from my experience, there is zero difference in VBA coding when developing as mdb vs adp, it's just very cool and functional to do so! : )
I haven't looked at a lot of ADP's but the few I have looked at used lots of code behind forms to manage recordsets. You would never see that in a standard Access app that uses bound forms. Perhaps the ones I encountered were atypical.
I use non-Jet/ACE BE's for most applications. The reason I never adopted the ADP was because it couldn't link to anything except SQL Server which limits its usefulness in the kind of apps I seem to develop. Many of my clients use RDBMS other than SQL Server such as DB2, Oracle, and Pervasive and the ADP doesn't support them. So, from the early days, I went with linked tables and never switched. Occasionally I need to create a view to speed up a join or a pass-through query to do a bulk update but forms bound to queries that restricted the rows returned always worked well enough.
I use non-Jet/ACE BE's for most applications. The reason I never adopted the ADP was because it couldn't link to anything except SQL Server which limits its usefulness in the kind of apps I seem to develop. Many of my clients use RDBMS other than SQL Server such as DB2, Oracle, and Pervasive and the ADP doesn't support them. So, from the early days, I went with linked tables and never switched. Occasionally I need to create a view to speed up a join or a pass-through query to do a bulk update but forms bound to queries that restricted the rows returned always worked well enough.
<<OMG, my VB code is all ADO, is that going away too!??? If so, this is the first I've heard of it. Please say it ain't so! : ( >>
To clarify, ADO is not going away, but OLEDB is. Just means that you'll need to switch the provider (it's back to ODBC).
I'm like Pat; I've always used linked tables via ODBC and pass-throughs for non-JET backends.
I do know however where you'll have your biggest issue is with forms (and Reports); you can't set a forms record source directly to a stored procedure, unless your doing it in code via a recordset. That will be the biggest change. VBA code will work fine with no change.
The good news is you can do everything your doing now, just needs to be done a bit differently.
I'd also add that there are no short-cuts here as gustav said.
No points please.
Jim.
To clarify, ADO is not going away, but OLEDB is. Just means that you'll need to switch the provider (it's back to ODBC).
I'm like Pat; I've always used linked tables via ODBC and pass-throughs for non-JET backends.
I do know however where you'll have your biggest issue is with forms (and Reports); you can't set a forms record source directly to a stored procedure, unless your doing it in code via a recordset. That will be the biggest change. VBA code will work fine with no change.
The good news is you can do everything your doing now, just needs to be done a bit differently.
I'd also add that there are no short-cuts here as gustav said.
No points please.
Jim.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial