Upgrading from ACCESS (many users + slow server)

[pablotx]

Howdy everyone. Thanks for all of your help in the past. This website is a great resource for designers.

I have a pretty broad question. I have been building some ACCESS databases for my department for a few years. They worked great for years as our whole unit was in one area, only had about 15 staff and the designs were relatively simple.

As we have expanded, we now have 80 staff, more complex databases which are entry intensive, staff all around our state with varying network connection speeds, etc. We have some folks who have AIR cards with VPN access into our network.

Now the databases are slower than molasses unless you are physically connected to the network in the city where the network resides.

I am looking for solutions. I don't think revamping the entire network is an option. I understand that ACCESS is not really the optimum application for systems this complex and convoluted. Should I go the Oracle route, Internet based, etc? Another issue is that we have HIPPA sensitive information. Is there a way that I can keep at least some of the design or would it need to completely change?

I only know ACCESS, and am not incredible even at that. By using this website, I have been able to muddle my way through creating and maintaining our systems. So any information anyone can provide will be beneficial. Please make it as elementary as possible so that I can explain it to my supervisors.

Some suggestions I have already received are:
SQL Server
Oracle
Active Server Pages
Terminal Service Server

Are any of these significantly better or is there a workaround I can try with ACCESS?

Could I give everyone local copies of the database and then consolidate them occasionally? The current database has a lot of bound forms and have a lot of user entry.

Thanks so much. Sorry for the long post, I am befuddled.....

 

[gemma-the-husky]

terminal server is the best solution.

the problem with access is that all the data to be processed has to be transferred to the client pc - if this is not over a LAN, then the speed degrades drastically.

using a TS via a remote desktop connection changes this , as the TS is (should be) working with a local connection. The only issue is making sure the TS can see the end PC to save and print locally, if required.

Changing to client/server with SQL server might also help - but if the front end is still access - the system will still need to transfer the same amount of data, and the savingsd might be illusory. - you can reduce the amount of data by using stored procedures etc on the backend - but this oftens needs you to reengineer the project, to achieve best results.

 

[boblarson]

And I hope that you are using SPLIT databases with the backend holding the tables and a COPY of the Frontend on each user's machine. You do not want to be having multiple users using the same mdb/accdb file on the network.

Also, you might want to check these two articles by Access MVP Albert D. Kallal:

Splitting your database

Access on a WAN