Upgrading Microsoft Access 2007 to Access 2013 Best practice

In my company we are having an Microsoft Office 2007 installation. With a production Access 2007 database. This database is divided into a backend database with data-tables (linked Access 2007 tables) + a lot of ODBC linked tables to Oracle Tables + Linked Excel Sheets (Excel 2007). And there is an Access 2007 Front-end which is used by fairly thirty users. And in Addition a lot of vba-code offcourse.

Additional we are using a central workgroup information file (mdw).

What is the best practice for an upgrade to access 2013?

Will the front-end database could be upgraded to Access 2013 without upgrading the backend databases at first ?

I guess all users will need to have upgraded their Access to 2013 at the same time before accessing the upgraded frontend ?

What about vba code compability ?

What about the central workgroup information file (mdw). ?
RonniLykkeHansenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
User Level Security has long been deprecated, and in fact you must still be using the .mdb format in order for you to be using ULS. The new .accdb format doesn't support ULS, and will basically remove it (or ignore it). In this case, I'm not sure what the benefit would be to moving to 2013, and in fact you may find some barriers in regard to using the .mdb format in this setup. I would strongly advise testing to thoroughly insure your proposed environment will function correctly.

Regarding best practices - as stated earlier, it's well past time to move away from the .mdb format. The .accdb format is robust and it seems the bugs have been worked out, and it's stable. If you're using ULS for actual data security, then you were simply fooling yourself anyway (it offered no real protection). If you were using it for user navigation (a much more common use), then you can always create your own "users and groups" setup, and manage navigation in that way. Regarding the actual upgrade, in my opinion, you're better off creating a new 2013 database and importing everything into that new database, rather than using the Upgrade method in Access. This seems to solve a lot of ills, and typically results in a more robust environment.

2013 can connect to the 2007 database - there's been no format changes in the .accdb file format, at least that I'm aware of, but it would seem you're still working with the .mdb format if you're using ULS.

In general it's best for everyone to be on the same version of Access, but it's possible to run a "mixed" environment if you do things right - that is, everyone is AT LEAST at 2007, and you are NOT sharing the FE between users (i.e. each user has their own copy of the FE installed on their desktop). If you're sharing the FE between users, then all bets are off!

I don't know that you'll have any code issues moving from 2007 to 2013, but it's hard to say. I would strongly advise you to setup a testing environment first, and try your upgrade there with copies of your system. Run through all the major functionality of your system to verify it before rolling it out blind.

As stated earlier, it's time to get rid of ULS and move to a more robust data platform (if your concern was data security) or build your own user management system. If you move to the .accdb format you're going to be forced to do that. If you don't, then leave the mdw file where it is.

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I don't know that you'll have any code issues moving from 2007 to 2013, but it's hard to say.>>

 Usually it's just a few minor ones.  Micrsoft tightened up on some things which in the past you could get away with, but not now.

 In addition to what Scott has said, make sure your app compiles cleanly before you try to convert.

Jim.
RonniLykkeHansenAuthor Commented:
@Scott: Thanks for fine detailed comment. It leads me to the next questions :-)

1. Just to be sure, what do you mean by "FE". Could you please explain what yo mean ?

2. To get rid of the ULS. What would you propose ? I hear you say that ULS is "bad practice". The ULS is used here just to keep people out. More precise so people dont click by accident and opens the actual frontend database, and eventually ruin something.

3. Was it possible to have the Back-end databases in Access 2007 format while upgrading the Front-    end to the Access 2013 ? Then at the end when everybody is running Access 2013 front-end. Then finally upgrade the Access 2007 back-end databases to access 2013 format.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FE is the "Front End" - in a typical multi-user setup you'll have a Backend (tables only) and a Frontend (everything else), and you'll distribute COPIES Of the FE to your users. Each of those FE files will connect to the same BE File.

I didn't say ULS was a bad practice, but rather this it's no longer supported in newer versions of internet), Access. Used correctly, it was an effective tool to keep most users out of your database design. There were some gaping flaws  (you can break ULS with a credit card and about 3 minutes of search on the internet) but for less-than-savvy users it was a good way to keep honest people honest.

There is no replacement for ULS. If you need to provide data security, then you'd have to move to a more compliant database engine. like SQL Server, Oracle, etc, and manage data security using the mechanisms provided by the engine. If you need "user navigation" - that is, UserA can open Form1 but not Form2 - then you'll have to create your own methods to do that. There is at least one package that can mimic ULS for you here:

http://www.peterssoftware.com/las.htm

Note the above software provides only user navigation - it does not provide any form of data security.

There have been no changes I'm aware of between 2007 and 2013 in regard to the actual data storage mechanism of the .accdb format. There have been quite a few changes in Forms/Reports etc - but a BackEnd contains tables only, and therefore none of those changes impact your backend. So yes, a 2013 FE can connect to a 2007 BE.

BUT ...

If you're using ULS then you are still using a .mdb format. I cannot stress this enough - you must fully understand your architecture before you being making decisions as to upgrading.
RonniLykkeHansenAuthor Commented:
Thanks for your input. It was very helpfull for my further planning on upgrading the Office 2007 database to Office 2013
RonniLykkeHansenAuthor Commented:
@Scott:
You wrote:
"There have been no changes I'm aware of between 2007 and 2013 in regard to the actual data storage mechanism of the .accdb format. There have been quite a few changes in Forms/Reports etc - but a BackEnd contains tables only, and therefore none of those changes impact your backend. So yes, a 2013 FE can connect to a 2007 BE."
 
What about the other way. Any experience or Knowledge about the other way 2007 FE and 2013 Backend database. What that do ?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Should work fine, since a backend contains only tables. As mentioned, there's no structural difference between a 2007 db and a 2013 db. They both use the same .accdb format, and as far as I know there's been no change between the .accdb format between 07 and 13.

Remember that Access is really a combination of various programs. There's a database engine (known as the ACE engine), a Form generator, a Report generator, Query designer, etc etc. Changes in one section of that may or may not effect other sections, so it's entirely possible for the Access team to update the Report generator without touching the ACE engine.
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.