Convert MS Access 2000/2003 db/application to Access 2010

I have been asked to convert an Access 2003 db/application to Access 2010 (may be able to stay at 2007)

What issues can I expect?

Will the tables convert cleanly and it is only the code that may cause issues?

I haven't seen the database as of yet.  I believe it is relatively small with minimal code and just a few tables.

LVL 102
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.

Kelvin SparksCommented:
Generally, they will convert cleanly, unless you've got some add in controls. These could need some attention (date pickers etc...)

You should be able to go straight from existing to 2010.


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
Jeffrey CoachmanMIS LiasonCommented:

Hey nice to see you in the Access zone...

To be clear are you actually converting to the .accdb format, of just upgrading Access?

Just note that after you convert to the .accdb format, you may not be able to convert back to the .mdb format.

So unless you need a feature specific to the .accdb format you can always keep the db in the .mdb format for a while... (until all users have acc 07 or higher installed)


mlmccAuthor Commented:
I know about the accdb vs mdb issue.

I haven't seen the database yet so I amnot sure what all it has.  A potential client has asked my company for help.  Apparently they tried to upgrade to 2007 (not sure if it was just open in Access 2007 and run or to an accdb) and apparently they had some issues.

We have been asked to give them an estimate of the effort involved and I just am trying to anticipate if there will be any real issue.  I know I ran a fairly complex (lots of code and screens) database as an mdb for a long time before we changed it over to accdb.

I don't recall any real issues occurring while it was an mdb.  I don't recall if it was originally a 2003 app or if it was built in 2007.  I took it over when they wanted to use 2007.


Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
..ok, great, then it looks like Kelvin has you covered...
mlmccAuthor Commented:
Thanks.  I didn't want to say 10 hours when there is a good chance it is 100 or 100 when it is most likely an easy task.

I'll ask for more information after seeing the database and when I have more information on it.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've converted quite a few, and in most cases it's painless. Kelvin points out the non-compliant control issue, and that can be a big factor in FE databases.

I also ran into some code snags. Access was very "code friendly" in earlier versions, but starting in 2007 things seemed to become a bit less loose in terms of usage. For example:

DoCmd.OpenReport "ReportName", , "MyField=MyValue"

Would most often fail, since the "MyField=MyValue" is in the Filter argument (which expects a query, according to MSFT documents). In earlier versions this worked, but starting in 2007 it failed. The fix was to move the criteria over to the Where argument:

DoCmd.OpenReport "ReportName", , , "MyField=MyValue"
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Adding to LSM's comment, there are places where 2007/2010 are also picky about reference controls and fields.   If your DB has the ! to refer to an object and . for a property or method, then you won't have issues.

But if it uses a . for everything and control and field names in forms are named the same, you may have some cleanup to do.

Also, with the conversion to .accdb, I'd avoid that as Jeff said unless you have some specific accdb feature that you need (and frankly, there's nothing there worth converting for).

 If you do switch, be aware that user level security is stripped out automatically.  Also stay away from the attachment data type; it's not upsizable to SQL server and it's difficult to work with (not a lot of programming support).

 Also you will loose any menus you have if you switch (if you don't switch, A2007/2010 will display them just as they are now).

 Last, with 2010, be aware there is a 32 and 64 bit version of Office.   You want to stick with the 32 bit version, which will run under a 64 bit OS without issue.

Jeffrey CoachmanMIS LiasonCommented:
Yes,...Please continue with the experts here who are working on your direct issue here.
I was just wondering about the number of fields.
You can post another question about that later...

mlmccAuthor Commented:
They want to upgrade to ACCDB format but find that some of the features they are used to have changed.

For instance
They open a table
They right click a data element and see the empty filter box.

In the ACCDB the box isn't there but other options they don't understand are presented.

If they leave the database in MDB format are the right click options the same as they were in Access 2003 or do they change to 2007/2010 menus?

They have a bunch of macros to do the data manipulations they need for doing the payroll.
For instance one they mentioned clears a table and imports the payroll data for the last 2 weeks.

Are macros like that affected by the upgrade to 2007/2010?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, the new features will be present regardless of the format. I'm working with an older 2000 file, and when I open a local/linked table in Datasheet view I see the same interface I do when working with an .accdb file.

Many of those features have been revamped. For example, the Search features you will find in a Table datasheet view are (IMO) much more robust. If you rightclick on a field, you'll see a menu selection named "Text Filters" which gives you quite a few options for searching the contents of the column. There are also frequently used filters, like "Does Not Equal xxxx" (where "xxx" is the value in the selected box). All in all, MSFT did a pretty good job of providing more user functionality in views like this.

Like everything else, the new UI is one they'll have to get comfortable with.

Are macros like that affected by the upgrade to 2007/2010?
Generally speaking macros which do nothing but manipulate data have no troubles. Macros that call out to VBA functions could run into the same issues you would find with VBA code (i.e. the OpenReport issue, or the ! vs . issue).
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.