Upsizing?

[Jay Cook]

Hi All

I was wondering if I should upsize to SQL backend or not - I currently run a
front end back enviroment, the backend right now is only 100mb and the front
end is about 32mb, I have users across the country using it (between 20 and
30) concurrently - some of the forms take a long time to load also I have
seen some data corruption. Should I upsize to SQL backend? will I see much
performance increase? or should I go full out and convert to stored
percedures and so on

Thanks for you help

Jay

 

[Armen Stein]

Hi All

I was wondering if I should upsize to SQL backend or not - I currently run a
front end back enviroment, the backend right now is only 100mb and the front
end is about 32mb, I have users across the country using it (between 20 and
30) concurrently - some of the forms take a long time to load also I have
seen some data corruption. Should I upsize to SQL backend? will I see much
performance increase? or should I go full out and convert to stored
percedures and so on

Thanks for you help

Jay

Hi Jay,

There are a variety of considerations here. If the users are connecting
via a relatively slow WAN (~512k), then I'm impressed that it's working
tolerably at all. If you've got good speed (let's say 10Mbit), then it
can work, but with the problems you are seeing. Access/Jet, as a file-
server database, just wasn't designed to work over long or slow network
connections.

The users should each have a local copy of the front end.

I think you're on the borderline of considering SQL Server. You could
certainly get much better performance and no more corruption. We
successfully run Access/SQL Server client-server applications over DSL
connections. However, to do this you need to optimize performance using
passthrough queries, views, stored procedures, functions, etc. It's
more work, but the end result is much more scalable and easy to use.
Plus, you get all the other benefits of SQL Server - triggers, live
backups, better security, etc.

Other ideas:

- build a web app instead, at least for the portions that the remote
users need to use
- continue to use Access, but have remote users log into the server
using Remote Desktop (RDC)

Hope this helps. Let me know if you have further questions.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com