Access 97 conversion

[Michael Allen]

We have an Access 97 database application which has been in operation for
about 5 years. It has a front end /back end architecture.

The database has been running very successfully over this period but we now
have approximately 70 users running it over our network and we realise that
we may be reaching Access's 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 Visual Basic.Net but maintaining an Access
back end.
2 Rewrite the front end in Visual Basic.Net and convert the back end to
MS SQL Server or MySQL.
3. Stick with the Access front end but convert to the latest version of
Access. This would be a very expensive option as we would have to buy the
latest version of Office (over 70 licenses).
4. Produce an Access executable using Office Developer edition.

Can anybody give an opinion on the best course of action? Will future
versions of Access be sufficiently robust to support a larger user base or
should we convert to a different system.

Thanks,

Mike

 

[nobody]

We have an Access 97 database application which has been in operation for
about 5 years. It has a front end /back end architecture.

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

These are:-

I presume that your back-end is a file stored on a file server and shared by
your front-end.

1. Rewrite the front end in Visual Basic.Net but maintaining an Access
back end.

Does not change application architecture, you would use the same (or
updated) Jet engine in the file server environment (with the simmilar
limitation as the current system).

2 Rewrite the front end in Visual Basic.Net and convert the back end to
MS SQL Server or MySQL.

I would recomend PostgreSQL over MySQL as a back-end database.

3. Stick with the Access front end but convert to the latest version of
Access. This would be a very expensive option as we would have to buy the
latest version of Office (over 70 licenses).

Again, no change in system architecture so even the Jet engine would get
updated, the file server architecture remains.

4. Produce an Access executable using Office Developer edition.

No change to system architecture (file server remains).


You have an option to convert the back-end (tables and sata) to a database
server (MS SQL or PostgreSQL) and have the MS Acces front-end use it over
ODBC linked tables (and pass through queries). It will very likely require
re-development your front end to work efficiently in the new client/server
environment. The PostgreSQL admininstration tool (called PGAdmin II) has a
Database Migration Plugin which can convert MS Access database (table
definitions and data) to PostgreSQL database. So to begin with, you can
convert your back-end to it and link the front-end over ODBC.
I have recently converted MS Access database to client/server (db server is
PostgreSQL) where the Access front-end tables are linked through ODBC. Most
of the front-end works but some of it is very slow. I have redeveloped parts
of it to use pass-through queries and it got fast (really fast). (To
redevelop the whole front-end will take some time in my case.)

Also, you could move some of the application logic into the database (in the
case of PostgreSQL).

Can anybody give an opinion on the best course of action? Will future
versions of Access be sufficiently robust to support a larger user base or
should we convert to a different system.

File sharing (especially the case of 70 people sharing the same database
file) does not support large user bases. It is inherent in the principle of
it. Converting to client/server should give you ability to support more
users than file/server provided the database server machine is big and
robust enough.

 

[Brendan Reynolds \(MVP\)]

We have an Access 97 database application which has been in operation for
about 5 years. It has a front end /back end architecture.

The database has been running very successfully over this period but we now
have approximately 70 users running it over our network and we realise that
we may be reaching Access's 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 Visual Basic.Net but maintaining an Access
back end.

The database engine (the 'Access', or more accurately, JET back end) is the
major factor influencing the number of concurrent users that can be
supported, so this would not, in itself, make any significant difference.
You could, of course, design the application differently in VB.NET (using
unbound forms or disconnected datasets) which might make a difference, but
simply rewriting the existing application in VB.NET will not make a
significant difference.

2 Rewrite the front end in Visual Basic.Net and convert the back end to
MS SQL Server or MySQL.

Converting the back end to SQL Server would help. The rewriting of the front
end, however, would, unless the application is a very simple one, take a
long time (or multiple developers) and therefore cost more, by an order of
magnitude, than 70 Access licences.

3. Stick with the Access front end but convert to the latest version of
Access. This would be a very expensive option as we would have to buy the
latest version of Office (over 70 licenses).

If this would solve the problem, it would actually be a much less expensive
solution than 1. or 2. But in fact it would not, in itself, solve the
problem. Later versions do include some features that you may find helpful
(see the comment about ADPs below) but the app will need to be modified to
make use of those features - simply converting the existing application to
the later version of Access will not, in itself, help at all.

4. Produce an Access executable using Office Developer edition.

The Office Developer edition (or equivalent, the name tends to change from
one release of Office to another) does not produce an executable, it
provides you with the tools and licence required to distribute a 'runtime'
version of Access (Access with design features disabled). It would enable
you to move to a later version of Access without buying a licence for each
user, but moving to a later version of Access will not, in itself, solve
your problem, and the cost of 70 Access licences is really not what you
should be worrying about here - not while you're considering a complete
rewrite of the application in VB.NET.

Can anybody give an opinion on the best course of action? Will future
versions of Access be sufficiently robust to support a larger user base or
should we convert to a different system.

Access is already sufficiently robust to support a larger user base, it is
the database engine that is the issue here. An Access front-end with a JET
back-end *can* handle 70 users, under the right conditions. So it may well
be worth your while to have an experienced Access developer look at the
application to determine whether your problem might be solved by optimising
the existing application or the conditions under which it operates.

Alternatively, another option is to convert the back end to SQL Server and
link the existing front end to SQL Server using ODBC. This option requires
the fewest changes to the existing front-end, but at the same time there is
a lot that can be done to optimise this type of application going forward,
by identifying bottlenecks and determining whether the processing can be
moved to the server using pass-through queries or stored procedures.

Another alternative is to upgrade to a later version of Access and rewrite
the front end as an Access ADP, which provides a more direct connection to
SQL Server, without going through ODBC. This involves considerably more work
than using ODBC, but still a lot less than attempting to recreate the Access
application in VB.NET.

 

[Michael Allen]

Thanks very much for your rapid response Brendan and 'Nobody'. Your
comments are extremely useful and you concur on a number of points enabling
us to focus our thinking.

One other option we may be considering is to maintain the Access front end
and converting the back end data to MySQL. The front end is a very complex
application and as you say, Brendon, a rewrite of this in VB.Net would be a
major task which could take say 3 years with two developers costing may
times more than 70 Access licences.

Would it be possible to convert the front end to the latest version of
Access without a major rewrite? Our problem is that if our staff numbers
increase we will not be able to purchase further Access 97 licenses as these
are no longer available. I know that we can purchase say Office XP licenses
which would still allow us to use Access 97, but is seems that one way or
another we are going to have to upgrade to Office 200x. I can't see us
still using an Access 97 front end in 5 years time.

I would appreciate your further comments.

Regards,

Mike

 

[John Vinson]

Would it be possible to convert the front end to the latest version of
Access without a major rewrite? Our problem is that if our staff numbers
increase we will not be able to purchase further Access 97 licenses as these
are no longer available. I know that we can purchase say Office XP licenses
which would still allow us to use Access 97, but is seems that one way or
another we are going to have to upgrade to Office 200x. I can't see us
still using an Access 97 front end in 5 years time.

Upgrading A97 to A2002 has some minor quirks (for instance, A97
defaults to the DAO object model whereas later versions default to ADO
and require explicitly including the DAO library); but an upgrade is
anything but a "major rewrite". I've upgraded several working 97
databases to 2000/2002 with no problems, and a couple more with minor
fixes.

Of course if you want to take advantage of new features in the later
versions, you'll need to put them in; but the existing A97
functionality should upgrade very readily.

 

[Brendan Reynolds \(MVP\)]

Most Access 97 apps should convert to a later version of Access without any
great difficulty. Keep in mind, though, that Access 2002 requires Windows 98
or later, and Access 2003 requires Windows 2000 or later, so some PCs may
need an OS upgrade as well.

 

[Sean]

I'd convert the backend to SQL server (licensing costs, you might want to
check, 70 users will cost a fair bit, so you decide, or MYsql or some
variant of)
Basically, convert the backend to a real database engine and stay with the
same front end as this will reduce changeover costs and time.
Just change a few bits of data access code in the front end and your up and
running.

 

[Sean]

and oh yeah, buy 1 office developer and use the royalty free distribution
of the runtime environment, for the version you buy. You may want to
convert up to 2K before deploying or even better XP.

 

[Robert]

Hello Michael.

You might want to approach this project in phases:

1) Upgrade the front-end to Access2002 or Access2003 and deploy using the
office developer kit (you can leave the back-end in Access97 format during
the deployment until all users have been upgraded to the newer version of
Access).

2) Upgrade the back-end to Access2002 or Access2003.

3) If the performance is not up to expectations, hire an experienced access
developer to optimize/fine tune your forms and queries

4) If the performance is still not up to expectations, then convert the
back-end to sql server and use stored procedures to let the server do the
work.

There's a good chance that the 3rd step will satisfy your needs for a long
time without costing you much. I've done quite a few Access 97 upgrades, and
would be glad to take a look at your database and give more specific
recommendations if you're interested.

Robert Smith
Kaizen Software Solutions
http://www.kzsoftware.com/services/msaccess/