Have any advice for a Small Business with Access 97 DB?

[dylfar]

Hi All,

I'm kind of at a crossroads at work, and I was wondering if you guys, being experts in the field could offer any help or advice.

I work at a small business (20 employees) with a relatively simple Access 97 program we use to quote custom jobs. We have the backend on a local server, and frontends on users' PCs, both XP and Windows 7 (Took a lot of effort to get Access 97 to work on Win7)

I have built another program in Access 2010 to perform another task (I'm an engineer, not a full-time programmer), and was assuming I could install Access 2010 Run Time on users' PCs (I am the only one with Office 2010) and they could use both Access 97 and Access 2010 Run Time. As I'm sure you realize, this has caused problems.. For instance, Access 97 has stopped working, and will not uninstall or reinstall on Win7.

Is it time to buy Office 2010 for my users and have a developer come and convert the legacy program to Access 2010? This issue is compounded by the fact that Office 365 is now out.. But since I have XP machines, Office 2010 is the way to go, right?

Any help or advice you guys could offer would really be appreciated! Thanks in advance!

dylfar

 

[CJ_London]

I would invest in 2010 and upgrade your 97 db front end to 2010. The upgrade should be pretty straightforward (basically compile and adjust any user preferences re tabbed v overlapping windows etc) and you can keep the backend as .mdb. With a modicom of sense you should be able to do it yourself without calling in a developer.

The main thing to ensure is you are not mixing 64bit office and 32bit office - although they should be the same you will need to compile 32bit and 64bit separately, particularly if you are using .accde. (for preference stick with 32bit until the bugs are fully ironed out)

 

[boblarson]

Sometimes you just gotta move forward as older versions get older and older. It is the way of Microsoft (and most software companies as they can't ensure compatibility forever). The one problem with waiting so long to upgrade is that it sometimes becomes more of a major rewrite than incremental.

And to echo CJ - get 32 bit Office - unless you have massive calculations on massive spreadsheets, 32 bit will be the way to go for now as the 64 bit introduces its own series of issues which can be a pain.

 

[Pat Hartman]

If you end up upgrading Office, I would go with O2013. Why spend all that money on 3-year old technology? I don't know if O2013 comes in 32/64 bit versions but if it does, stick with the 32 bit all around (the Access versions are not compatable). Both versions should both be on the CD. In a couple of years when the bugs get worked out and all the addins get republished in 64 bit versions, you can "upgrade". For now, the only thing the 64 bit version gets you is humongous spreadsheets and I'm not sure that is actually an advantage.

 

[spikepl]

AFAIK Office 2013 does not run on XP. Officially, Microsoft's XP-support expires mid-2014, so you might have to upgrade those machines anyway ...

 

[gemma-the-husky]

A97 does not run natively on W7

A2003 does

after A2003, the menubars change to ribbons, which use more screen space on especially on a laptop - and the database window is less user friendly, in my opinion

A97 should certainly convert without problems.

 

[Pat Hartman]

Am I the only one who hates the short and wide screens? Laptops are especially a problem. I've had to redesign almost all my Access apps because my clients are retiring the big "square" monitors and all that you can buy now are the rectangles.

I appoligize for hijacking the thread to rant.

 

[ChrisO]

I don’t think I’m a Luddite but I also don’t see change for the sake of change as a good thing.

Simply because Microsoft may withdraw support for a product that doesn’t mean it will crash overnight. When support is withdrawn from a product the product becomes stable simply because nobody is changing it.

If an application is working fine then there is no need for the new ‘bells and whistles’. It’s the ‘working fine’ which should be the criteria not the ‘bling’ which comes with the upgrade. And even the word ‘upgrade’ is misplaced; it can hardly be called an upgrade if it causes an application failure.

The IT department of companies I have worked for, and at, have all been reluctant to install any software if that software could not demonstrate some value. That even goes for service packs and/or revision levels. (These days ‘service packs’ means CD/DVD or downloads, a few years ago it may have meant fusible link ROM chip replacement. Whatever.)

That principle should still remain the same today. If there is no need for change then there is nothing for the application to gain. The company can not increase the profit from their application from no change.

What could happen, though, is that the change, which was not required, may break their application and decrease profit. Hence the company, when making a change, faces the problem of no increase in profit but a possible decrease in profit with their application.

So from the point of view of change with no gain, a company is ‘On a hiding to nothing’; no chance of profit but only a possible chance of loss.

----------

You will get people who say software will not last forever, and that is true. But it is also true that nothing lasts forever so the previous statement is flawed. What we should be looking at is the expected life of an application which is ‘working fine’.

The question then becomes; what is the expected life of an application which is ‘working fine’? Well, software does not age, only the expectations of the people using that software age. We could quite reasonably say that, without the changing expectations of the people using the software, the software actually gets better over time. It gets better over time because it gets faster…Moore's law.

Moore's law is still in force; however Moore's law tends to get swallowed up by ‘bloat ware’ and ‘bling’. If you ever get the opportunity to run an older application running under an older operating system on a new machine, you may very well actually see the difference.

----------

Microsoft removing support for a product does not define the end of life of that product. Nor does it mean the end of life of your application which relies on that product. What the removal of the support for that product actually means is the cessation of change of that product. That cessation of change means stability and improved performance over time due to Moore's law.

Provided that modern hardware can run your old software, which is ‘working fine’, then the only reasons to make changes are the expectations of the people using it.

Chris.

 

[boblarson]

If an application is working fine then there is no need for the new ‘bells and whistles’. It’s the ‘working fine’ which should be the criteria not the ‘bling’ which comes with the upgrade. And even the word ‘upgrade’ is misplaced; it can hardly be called an upgrade if it causes an application failure.

When you get too far behind though, it can cost you way more to fix it than it would have cost to keep up with the changes. Just because you move to another version of Windows and/or Office doesn't mean that the program necessarily has to change. But I have experienced firsthand, a program that someone wrote for someone in Access 97 which has served them very, very well. However, when the company was forced to move to Windows 7, the program broke. And I was hired to figure out why and to see if I could fix it, move it to 2007 because they (large corporation) needed to move to Office 2007 as the computers they could buy would no longer run the earlier operating systems and they could not acquire new licenses even if they could. So, as it turned out, the number of things deprecated over time that it had been using was so intense that they had no choice but to rewrite the entire program at a large cost including the fact that they had no way to use the program in the meantime on which a segment of their business depended.

So, the moral of the story is this - Yes, a program may work fine for you for a very, very long time. But if you wait too long, you may find yourself in a situation which can be more costly than if you had incrementally moved it up. The difficult situation is to figure out how long you can hang one without hanging yourself. And that is one of the things about running a business. Keeping tabs on risks to the organization. In the case of the company which I did the troubleshooting (fortunately I did not do the rewrite as I was only doing the troubleshooting part-time and had no way to even consider doing any of the rewrite), they paid big time for not managing the risk better.

 

[Lightwave]

I think Chris and Bob raise good points.

There is a central principle however of computer science that many software houses seem to be fighting against.

The promise of the digital age was and continues to be that information becomes almost free in that duplication costs are close to zero and there should be no deterioration of information over time and so systems could become immortal.

Good thing about having a long time to develop an application - you can usually get it totally nailed even on the fine points.

For many of the data driven adminstration procedures for which Access is good for there is a no fundamental need for re-invention. Basic book keeping theory hasn't changed since the 15th century neither has the fundamental principles of set theory on which SQL is based.

So you are left with legitimate reasons for upgrading
To take advantage of increased memory - eg 64bit
To take advantage of increased speed.
To take advantage of increased scope - ie the internet
To take advantage of increased ability to design.

Many users of acces haven't used half their potential memory are quite happy with the speed have no need for the internet and find access already quite easy to design in.
In such circumstances it is quite understandable that they really don't want to redesign everything just to get back to the position they were in prior to the re-design. Who after all in their right mind would want to replace St Pauls Cathedral with a new cathedral (They did a brilliant job the first time round)

Its not just Microsoft who are fighting against immortality of their products and the derivatives therein - Adobe , Alpha 5, ArcView and many other software houses are trying to move towards software as a service where they keep the knowledge of design and rent out their products as services.

I think you will find that many companies will move to longer and longer upgrade release dates and eventually they won't even mention it. It will be interesting to see how open source continues to develop in the long term this might be the most stable platform of all. Developments being led by need rather than requirement for profit - its a purer environment for incremental survival of the best.

 

[CJ_London]

BL

But I have experienced firsthand, a program that someone wrote for someone in Access 97 which has served them very, very well

Same thing happened with me - I wrote soemthing in A97 about 12 years ago, they have now updated to W2007 and Office 2010 (64bit) which has required a complete rewrite.

In addition, other things move on, email was barely used back then (at least by my client), the web even less so so additional functionality is now required. Another problem was the change for Excel from .xls to .xlsa. The upgrade provided by MS so Excel 2003 could read .xlsa files does not extend to Access (at least I never found a way!). OK Excel users can still save as .xls but they often forget.

 

[gemma-the-husky]

I struggle slightly with this idea of complete rewrites

I tihnk A97 had very few imperfections. From A97 you could do pretty well everything you wanted. A2003 and later versions have added a few bits and pieces to make life easier, but nothing that couldn't be achieved in A97. Eventually I changed to A2003 as standard as no-one was using A97 anymore.

A97 apps convert to A2003 with no problems, and then to A2007/A2010 also without apparent problems. Ok, if you develop in A2003, you miss out a few A2007 functions - but nothing major as far as I can see, so I struggle to see why anything that worked in A97 would need substantial change for later versions.

 

[boblarson]

Dave - use of third party tools for example, deprecated items that may be used like pulling data from a Db2 file, using ActiveX controls like the calendar control (I know that wasn't in 97). But just because you didn't use certain features doesn't mean others didn't. I don't remember the exact issues as it's been quite a while but there was enough that a lot had to be redone. Not table structure though.

 

[gemma-the-husky]

I see what you mean, Bob.

yes, there could be a lot of pain in cases like that.

 

[dylfar]

All,

Thanks very much for the insight. I kind of agree with all of you.. there's nothing "wrong" with our Access 97 database, but letting it go unchanged through each iteration of upgrade just creates an issue that grows with time. I don't think I can sell my bosses on upgrading to Office 2013, since it doesn't run on XP. I'll try and get the A97 database up to 2010. I tried going direct, but that didn't work out.. Am I to understand that people have faith that A97 to 2003 then to 2010 may have more luck? I think there's a copy of Office 2003 floating around here, I could try that.

Thanks Again!
Dylan

 

[gemma-the-husky]

if you get it coverted to A2003, you can also run it on a A2010 run time.

I presume it will also run on A2013

A2003 is good as it runs on both XP and W7 with no issues

 

[boblarson]

It can be tough in a corporate environment because the more machines that have a certain OS, the harder it becomes over time after support for that OS is withdrawn because new licenses for that older OS are not able to be purchased in sufficient quantity to stay on those older OS's. So it isn't just Office that has to be considered but also the Operating Systems. Like it or not, there is no advantage to Microsoft to keep the old stuff viable and so they do not want to keep the old stuff going.

 

[The_Doc_Man]

Here comes the gadfly...

There are a couple of reasons to consider as to why you really DO need to upgrade sooner rather than later... Microsoft's patch paradigm can update you to something that stops working for that really old stuff, particularly when you get automatic security updates and such.

Many of you don't play the "government security" game that I have to play, so don't see stuff happening to drag down your system. Can't remember the last time it happened, but with automatic updates turned on, it is not impossible for you to leave your machine running for nightly scheduled tasks and come back in to find that an automatic update just ate your lunch. And let's face it, lots of folks DO leave their machines running overnight for that automated defrag, automated updates, anti-virus update and scan, etc.

Basically, when Microsoft stops support for something, your security patches stop coming out. Check with your site's security policy before deciding that you need to ignore change completely. Because if you have data security issues, change is not merely useful - it might be mandatory. And don't forget that even if you really didn't need to change Access itself, you still use some shared Office libraries. If you needed a new Outlook or Excel for some strange reason, you are playing with fire when talking about mixed-version upgrades for those shared library files.

But on the other hand.... Access 2.0 was the version I first used and it is the one I fell in love with. Of course, like ALL such relationships, it quickly turned into a love/hate relationship. But about the time Borland changed from DOS to Windows for Paradox, I switched from Paradox to Access and never regretted it. (What never? No never! What never? Well.... hardly ever - with apologies to Gilbert and Sullivan.)

Ac97 worked like a champ even on my old, slow boxes. I'm on Ac2010 at home now, still working some of the taste of ribbons out of my mouth, but generally learning to live with it.

 

[ChrisO]

Dylan.

>>Am I to understand that people have faith that A97 to 2003 then to 2010 may have more luck?<<

I’m not so sure about luck but there may be a reason to go to A2003 before A2010, if that’s what you want to do.

A97 to A2003 can require a time consuming fix and it’s to do with the difference in syntax between the newer Microsoft DAO 3.6 Object Library and the older Microsoft DAO 2.5/3.5 Compatibility Library. If the A97 database uses the older DAO 2.5/3.5 Compatibility Library then it will be automatically changed to the newer Microsoft DAO 3.6 Object Library when converted to A2003.

That change in DAO version can’t be prevented during the change in Access version. It is almost certain that the new Access 2003 version will not compile.

There are two ways to handle that situation.
1. Change the Microsoft DAO 3.6 Object Library reference back to the Microsoft DAO 2.5/3.5 Compatibility Library reference.

2. Change the syntax of the DAO to comply with the syntax of the newer Microsoft DAO 3.6 Object Library reference.

Method 2 is preferred but it can take a few hours to do it; it depends on how much DAO code there is. No one will be able to say how much code there is, let alone say if the code is still used, but it will need converting, used or not.

The syntax conversion can’t be done automatically, it requires a compile, fix and save, compile, fix and save, compile, fix and save. It takes time but it is the way to do it.

Let’s then say you get A97 to A2003, but now you want to convert it from A2003 to A2010. In that conversion you may get unacceptable screen flicker over Labels or charts which don’t update or charts with unacceptable screen flicker on Mouse Move. Or it may be some other problem.

So the novice may come along and say ‘all you need to do is go to this or that site and you’ll get a fix’. Well you’ll get a fix for the Label flicker at AB’s site (maybe) but what about the charts? I can fix the Charts not updating but I can’t fix the screen flicker on Chart Mouse Move. So where do you go for that fix? Well, you don’t go anywhere, all you do is turn off that feature of your database. And it may be some other feature you need to fix.

So, at this point, you have a database which ‘works fine’ in Access 2003 but in order to go to A2007 or A2010 some feature of your database needs removing. It could hardly be called an upgrade if some feature of your database has to be removed. And remember, this was a database which was ‘working fine’ in A97 and again in A2003.

So, if you intend doing a conversion from A97 to A2010, I would recommend going from A97 to A2003 first while still running it under XP. The A97 version should have very little ‘bling’ which can not be converted to A2003 under XP. It may have a lot more code than a more recent version simply because it has existed for so long. Because of additions made, older programs tend to get bigger over time. People are reluctant to delete parts of those older programs simply because they don’t know what that section of code does.

--------------------

In essence then:-

You have an old program. That in itself is not a bad thing because it has stood the test of time. It’s old, but because it’s old, it is running without the ‘bling’ of the newer versions. It’s the very ‘bling’ of the newer versions which may cause it to fail.

Your current version works; so why subject it to a possible failure if there is nothing to be gained?

If you feel a need for change then take it to A2003 under XP. If you then have a problem going to A2010 (under whatever), you can easily go back to something which actually works.

Chris.

 

[Dick7Access]

I may be wrong about Office 2010, but when I tried to buy Access 2010 for a friend it was not available at computer stores. I bit the bullet myself and went 2013 and told hm if he wanted the db I was building for him he had to go 2013. BTW 2013 and 2010 would not run on my machine even after it was put in a different directory. 2010 run time would not run on his machine with 2000.