Starting the conversion from Access 2003 to 2010

Posted on 2014-08-12
Last Modified: 2014-08-13
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?
Question by:MrSlithy
    LVL 95

    Accepted Solution

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

    Author Comment

    should I use the default ACCdb fro both front and backends?  Not sure about SQL
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    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...

    LVL 33

    Assisted Solution

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

    Assisted Solution

    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
    LVL 1

    Author Closing Comment

    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?
    LVL 84
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now