Should I upsize from MS Access 2003 to MS access 2007 ?

Posted on 2011-10-14
Last Modified: 2012-08-14
Dear Expert!

I write a new version of my VB6 application which write data in Access database. Now I use file format Access 2002-2003 with MDAC 2.8 under XP and Win 7. For now it prove that work stable and I’m happy. The application currently work in many (more than 50 computers) since last 3 years.

My DV have about 30 tables , 10 querys and not many record, middle size 30-40 MB.
Rarely I get problem when power off suddenly, the DB file can be broken but usually Compact DB fix this.

So my question is – should I go to Access 2007 file format? I use only tables and few queries, no transaction at all. There only 1 limits in Access 2003 – I need text fields with let say 1024 symbol with Index features (I will search on it with Like command). With Access 2003 I have only 255 symbols.
Please share you experience. Do you have problem with migration and does new 2007 format is stable as old 2003?
Question by:dvplayltd
    LVL 8

    Expert Comment

    The 2007 file format is as stable as can be, and you should not encounter any problems migrating.
    LVL 61

    Accepted Solution

    Unless you have a specific requirement to do so, I would recommend saving the expense and keeping Access 2003 as long as possible.

    Not that I have any major issues with Access 2007, but from what you describe you don't really need the upgrade.  Your databases are not huge, and it sounds like they are pretty stable, and it doesn't sound like you need any access 2007-specific features - some of which I wouldn't recommend anyhow.

    LVL 61

    Expert Comment

    <I use only tables and few queries>
    I'm understanding this to mean that you are only using Access to store data and that your user interfaces are entirely in VB, or some other non-Access platform.

    <Do you have problem with migration and does new 2007 format is stable as old 2003?>

    If my understanding is  correct, you shouldn't have any trouble with a migration when the time comes.  The trouble I have seen some people have is in migrating to a different version of Access for the user interface.  The layout is completely different - commandbars (2003) versus ribbons (2007) present a learning curve for some.  Also, some code might not work in 2007 as expected and as it did in 2003, but correcting these issues is generally pretty straight-forward.

    The stability of Access 2007 seems to be about the same as 2003 in my experience.  One difference that I have noticed is that my files in 2007 compact down to a much smaller size than they did under Access 2003.  We have a few Access 2003 databases that compacted down to less than half their size when we moved to Access 2007.  There could have been other factors involved, and I haven't seen anything on the Web to support that observation, but I've always associated that with the move to Access 2007.

    Author Closing Comment

    10x and thank you for your time!

    Well, I ‘m not starting do change at all … first time I just ask. I’m 10+ in business and have enough other new features to add then just to replace equal things. And the rule – if it is works do not touch it is very correct in almost any cases … especially in my case where the application should work in mode 24/7/365 as this is software which create video signal for TV channels.
     My ask was because now I do new version and time if I do upzise of Access is now. But look I’ll says – viva Access 2003! Xexe :-) . You look me like real expert with Access and I’ll trust you that I do not need upgrade.
    All my interface is in VB6, you are correct. Starting since 1998  3-5 years I build my applications with Access 97/2000/2003  (data information system), so I grow with it ¿ Very good system, with great features allows to work quickly. In fact till now there no any other system gives such power so easy like Access. Now I use primary SQL Server and his UI is much worst than Access.

    But after that I start making software for international sales …  no more Access.  My option is that Access 2000/2003 was the tool number one for middle level programmers and perfect for beginner programmers to study principles.  In professional hand it also can do good working application. I do not know how is now with Access 2007 as all my new softwares are on Vb6 or C#, and in SQL Server too.

    Look to me that interface in Access 2007 has many change and in primary bad direction. I see many things that now I should spend more time than before.

    P.S: About - husband like third kid - it is happen also here in Europe. Cheers !!! jeje
    LVL 11

    Expert Comment

    I would concur with the others that there is no need to upgrade the data side to A2007, but when you start looking at doing something like this, I'd suggest looking as the (free) SQL Server Express.  There are many advantages to using this over Access as a backend.  It's much better for multi-user environments, better scalability, has features to better handle unexpected poweroff than Access db's (any of them), and more.  And it's very close to using Access as a backend like you are now.  A few things change, like the wildcards are % and _ instead of * and ?.  But you can use stored procedures, and many other aspects of SLQ Express that can even lighten up your app.  Just some other ideas to consider.

    Author Comment

    I know very well SQL Server express as I use it in other my application - data information systems. But in this case I'll never go to SQL Server as the possible problems here are much more than benefits I'll get from it.

    my aplication is resposible for produce signal for TV channels. It do this via software decoding, which is extensive use of CPU. If a service like SQL Server get CPU power for 2 sec. the TV signal can be broken. Also, the SQL Server installation not make any problems, but till now I never have problems with MDAC installation, in most cases they are already installed.
    LVL 61

    Expert Comment

    Just thinking about your scenario a little more...

    Your user interfaces, since they are not in Access, are not going to visibly show any difference to the users if you upgrade the back end to a different version of Access.  The layout will be the same and the performance will be the same.

    So in terms of upgrading Access, there is no reason too do it unless your clients want a front-end in Access -- where they would see a difference between versions.

    If you are just dealing with data, then size and performance should be the determining factors for an upgrade.  Your databases haven't reached a size that is unmanageable, and as long as the performance is good, why change?

    Also, if you ever do reach a point where your Access back-ends cannot meet your customers needs, you should indeed upgrade - NOT to a different version of Access, but to something completely different.

    <<P.S: About - husband like third kid - it is happen also here in Europe. Cheers !!! jeje >>

    Good to know that I am not alone ;-)

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now