The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 3
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.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
<<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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: IC3 GS4 Internet Core Competency Global Standard 4… 135 lessons
-
Microsoft ApplicationsCertification: MCP MCSA Microsoft Certified Partner & Solutions Ass… 377 lessons
-
Microsoft ApplicationsCertification: MCSA MCSE Microsoft Certified Solutions Associate & … 398 lessons
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Identity … 440 lessons
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Securing… 324 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.