Software·43,777 discussions

Access too slow over WAN

Locked

Our organisation has a large system compromising of 8 large linked Access XP databases (Human Resources, Grants, Projects, Publications, Staff Development and Staff Appraisals to name few). The whole system works great; that is, it worked great until recently. In the last two years we merged with two other units ? one last year and another this year) which made our current organisation reasonably large, both in staff member and area. We are now spread over an area of about 132,845 square kilometres and we found out that access database do not travel very lightly across the WAN, as a matter of fact they are unacceptably slooow.

We are currently looking at converting our entire Access database system to a system that?s most suitable for wide area distribution.

Most front-ends in the current system are linked to at least three to eight back-end databases in the system (using one or more tables from each liked back-end database). For example, Human Resources database, a critical database,is linked to every other database in the system.

DAO used throughout, larger number of action and selection queries with multiple parameters (most using dynamic IN and LIKE) and most queries created at runtime depending on user selection so we may find it difficult to convert them to SQL Views. The same applies with most reports; about 90% are generated in code. Quit a large number of our output is generated using Automation (outputting to Word/Excel throught code)

We need to develop a system that will be acceptably fast over the network and/or over the Internet. What do you suggest?

1) What back-end do you suggest and why (ie, SQL Server, Lotus Domino etc)?
2) What front-end do you suggest and why (ie., Visual .Net, Access ADP, HTML using ColdFusion server, Crystal Reports for report etc)?
3) Are there are any tool that will successfully convert Access MDB files with code and large number of linked tables from number of databases?
4) Are there are any pitfalls to look out for (when developing, converting etc)?
5) What about existing data (most databases in the system used Autonumber as the primary key; how will that come across to the converted system)?
6) Any other issue/questions/tips?

It looks like a huge job and I would love to hear suggestions from anyone who went thought the same process. As a matter of fact, I would appreciate suggestions from all the professionals in the forum. Any hint/tip are sought and appreciated?

Thank you

Topic