Access, MySQL, VB.NET

[Beetle]

We currently have a large database in MS Access 97 which has been in operation for a number of years.

The database has been running very successfully over this period but we now have approximately 70 constant users running over a network and we realise that Access maybe reaching in limits for such a large number of users. We are considering a number of options for future development/conversion.

These are:-

1: Rewrite the front end in VB.NET but remaining with Access97 backend.
2: Rewrite the front end in VB.NET but converting the backend to MySQL (MS SQL) is not an option due to cost.
3: Stick with Access front end but convert to latest version of Access. This would be rather expensive with over 70 licenses to purchase.

Can anyone please give an opinion on the best course of action VB.NET or Access??

 

[Ed2020]

If your only backend option is Access there is no point in rewriting frontend in VB.NET as you're still using the Jet database engine.

If the database is still running correctly I wouldn't worry too much. Some of my largest systems have had over 100 users. If the system is designed correctly then there shouldn't be a problem.

You might consider giving each user their own (locally stored) front end. Providing the front end only connects to the backend when necessary (avoid bound forms at all costs!) then all should be fine. Most databases actually spend only a small amount of time accessing the tables.

If you decide to upgrade avoid Access 2000 - it's buggy as hell!

Ed Metcalfe.

Please do not feed the trolls.....

 

[cheerio]

If Access does run out of steam it will be because the JET engine in the Access backend does not cope. The main problem is record locking which is the reason for avoiding bound forms. Stability is less a question of absolute numbers and more one of how many people might be trying to change data at the same time.

If you do need a redesign, the obvious answer would be to use an Access front end with a MySQL backend. This has the advantage that users would keep the same Access user interface.

I would see no advantage in moving users to a later version of Access in terms of system stability. If you are constrained by a desire to move to move to a later version of Office for other reasons you could consider VB.NET then. However, the first priority should be to get the back end right.

 

[Ed2020]

I fully agree with cheerio, but my initial thoughts are still "If it ain't broke, don't fix it"......

EM.

Please do not feed the trolls.....

 

[cheerio]

Ed

I think we basically agree. My concern is we do not know whether there is likely to be significant growth in user numbers. Neither do we know the likely lead time to introduce a change in the event that the system becomes stretched.

If it ain't broke don't fix it. However, if there is any concern about future direction I would put the emphasis on a stable back end to begin with.

However, it does need to be recognised that MS support for A97 is likely to be withdrawn as we now have Access 2000, Access XP and Access 2003.

Ken

 

[Beetle]

Dear All
Thank you for you input in this matter to answer a few questions.

1: Lead-time?
None Set

2: Why Change?
Access97 appears to very limited it what it can do. We will require new functionality within the system that Access may not be able to cope with. The current systems appear to run slower and slower the more functions we are adding.

3: User increases
It is very likely that the number of users will increase as the company expands.

 

[Ed2020]

VB front end with a SQL Server database sounds like a viable option to me. It will give you far more expandability in future than Access provides.....

Ed Metcalfe.

Please do not feed the trolls.....