Starting the conversion from Access 2003 to 2010

I have access 2003 databases running in an running in an AD environment.  about 10 or so Front end MDB.  Most of these linkt to a backend mdb file, and some of the more specialized one linl to several files.

The only security on the databases are the file permissions of the AD servwer. there is no Microsoft Access security file - MDW.

In the past, in connversion fro Access 97 to 2003 I converted all the FEs in one phase and then did the Backends in a later phase. But I do not thin I need to do this this time around - I'd prefer to get everything up on Access 2010.

I dont know if I want to keep working with MDB files are start using the new format.  I am in the process on reading up now, as well as setting up testing to see what breaks.

Any suggestions, pointers how to start?
Who is Participating?
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.

Lee W, MVPTechnology and Business Process AdvisorCommented:
I recently converted a complex database from 2000 to 2013 with minimal issues.  If you do mailmerges from any data, depending on the queries used, there could be issues.  There could also be issues using a calendar control no longer included - but googling the error reveals it's a relatively simple OCX copy/register to get it working again.

I would also STRONGLY recommend you upsize the database to a SQL install - use the free SQL Express - it's file size limit should be 5x what the Access size limit is anyway.

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
MrSlithyAuthor Commented:
should I use the default ACCdb fro both front and backends?  Not sure about SQL
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Copying and registering the ocx controls for stuff like Calendars works in many situations, but it's best to move to the new DatePicker control, or make use of one of the Calendar forms you can find around the 'net.

There are some gotchas that can cause some headaches. For example, some code that ran fine in older versions will break in newer versions, like this:

DoCmd.OpenReport "MyReport", acViewPreview, "ID=1"

That would run perfectly fine in 2003, but will fail in 2007 forward, since you're using the Filter argument instead of the Where argument. Instead, you would do this:

DoCmd.OpenReport "MyReport", acViewPreview, ,"ID=1"

Notice the extra comma after acviewPreview. This puts your WHERE argument where it should be ...

There are other issues as well, but in general it's a pretty painless upgrade.

I would also STRONGLY recommend you upsize the database to a SQL install
If you need to upgrade to SQL, this is definitely a good path, but there are many, many Access databases running perfectly fine with the .ACCDB format. Note that upsizing to SQL would move the Data ONLY to the platform (unless you want to move over Queries as Views, and move some of your processing over to Stored Procedures). You'd still have to use Access (or some other tool) to create your Front end objects like Forms and Reports.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Any suggestions, pointers how to start?
Make a back up of all your databases before conversion, ...

Another consideration many people miss is the distinction between the Access "Version" and the Access "Format"
Access 2007, 2010 and 2013 all support the .accdb format, ...however, not all .accdb format databases are the same.
For example, the Access 2010 accdb format contained many new features not supported by Access 2007, this caused a lot of problems, ...even with the dreaded "unrecognized database format" error occurring when opening an access 2010 .accdb in Access 2007

So here when you say "conversion from Access 2003 to 2010" need to consider if any rogue users may open the db in Access 2007. (or even an older version)
...or if the developer "snuck in" a 2010 feature (intentionally or by accident).

Also, is there a "Need" to upgrade?
This will be determined many things, ...among them:
The types of databases you are using
The complexity of the database
The number of users
The features you need

For me, about half of the databases I make do not need any of the new features in Access 2007 or above,.
But that is just me...
Your situation may call for an upgrade of the format across the board.

Perhaps some of the other experts can go deeper into this,...
...but with each new iteration of the database format, guess is that MS makes improvements and fixes bugs.
This may be the single most compelling reason to upgrade.
If the Access developers are seasoned,, ...I see no reason to remain in the past...

If it were me, I would convert the both the FE and the BE at "roughly" the same time.
For example, ...In the .accdb format there is a rich text (HTML) option in the database format.
If you try to set some of these features in the .accdb FE (ex.: form/report control properties), ...when they are not supported by your .mdb BE, ...I am not sure what the result would be.

So take all the advice of the previous experts to heart.
...again, make a backup of the databases before conversion and test each aspect of the database thoroughly...

I agree with Jeff, unless you have to worry about users having different Access versions which forces you to run parallel FE's until everyone is converted, converting the FE and BE together is probably easier on you.  Once you've converted an app, you're done with it and can move on.

I'm curious about why you are converting to O2010 rather than O2013.  Although I have a strong preference for 10 (and so do my users), it is "old" technology.  My preference for 10 is because of stability (I have a lot of issues with A2013 freezing on me).  My users preference is because they hate the Metro look (I don't like it either) of 13.  I'm in the process of creating new themes that "darken" everything to stop the complaints.
Luke ChungPresidentCommented:
I wouldn't worry about the new ACCDB database format unless you want the new multivalue and attachment field types.

Regardless of format, there are many features you can add to your Access databases once you go beyond 2003 into 2007, 2010, or 2013. Here's a paper I wrote about the things I could add to Access 2007 databases that I couldn't do in Access 2003 no matter how much programming or time I spent:

Top 13 Features of Microsoft Access That Aren't Available in Access 2003 or Earlier
MrSlithyAuthor Commented:
Thanks everyone - I will have many more questions as I progress and I will try not to "milk one question for all it's worth"

I think I will run the same way I did back with the Access 97 to 2003 upgrade years back.  I plan on making copies of the 2003 FEs and then converting them to accdb.  Then I will verify they all work with exisiting 2003 back ends.

Once this is done, I will deploy the new aaccdb FEs in parallel with existing 2003 mdbs, and then work on removing Access 2003 and installing Access 2010.  Then, I will upgrade the 2ndary backends to accdb as I go and then finally the master Back End.

I have a question about the old database password vs new encryption - - but that might can wait until I'm ready to do the BEs. Sound good?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your question about passwords and encryption should probably be asked in a separate question - but in 2007 and forward, the encryption techniques are much stronger than the older stuff, so a password-protected database is quite a bit more difficult to break into in the 2007+ format.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.