August 25, 2008 at 3:20 pm
A year ago I was asked to develop a database application for two individuals. Access was used due to the limited number of users and frankly thats all they were willing to provide me with. I have been asked to expand its functionality to several departments and enhance the functionality. I would like to move this application to SQL and perhaps re-write the front end in C#.
However I am beginning to wonder if this is overkill. The database file is less than 10MB after almost a year of use. My enhancements will not include any significant additions to the table structure. There is potential for 20 - 30 concurrent users, some of whom will be remote. Should I be looking at SQL? will my current Access application interface well if I do migrate to SQL?
August 26, 2008 at 7:13 am
That is difficult to say.
Some people have had good luck with small multi-user environments using MS Access or an MS Access / SQL Server combination. I have typically found issues and tend to stay away from it.
A lot of it will depend on how the application works. If there is a lot of users in list views that display a lot of records while other users are making changes, it is pretty easy to end up with blocking and lock conflicts. Also, if your network environment is such that some users get a rather poor connection, MS Access as a UI can become a problem with bandwidth.
The only advice I can give you is to do some testing. Try to get a real load on the application and see if it has any issues. Every situation is different. Will MS Access be able to handle it? Probably, but you may have to make some design compromises. You would probably be able to build a more stable and scalable application in C#, but you are going to have to weigh the cost-benefit.
August 26, 2008 at 7:56 am
you've got two things to weigh. First is the Access front end, which probably is fine. Rewriting it in C# would probably be a good learning experience, and great writing/blog fodder if you document it, but you'd have to decide if there's an ROI.
The db side, however, with 20-30 people, I'd move to SQL if there isn't a big cost. The reason is that you want to protect this data, handle concurrency better, etc. SQL does that much better. You can easily upsize the db to SQL, link your tables to SQL tables, and keep your Access front end. Easy is relative, but the process isn't that hard.
August 26, 2008 at 8:51 am
With 20-30 concurrent users, you will start to notice problems with Access and you will probably have to compact and repair nightly to avoid database corruption. A good fit might be SQL Server Express Edition, which can handle up to a database size of 4 Gig, and use up to 1 Gig of RAM sounds like it would be sufficient for your project. (and it's free!)
As for the Access front end program, you will want to investigate either changing all the queries to make sure they are pass through queries, or consider using the Upsizing Wizard. A word of caution on the Upsizing Wizard though, it doesn't always pick the best SQL Server datatype for your data, I've notice it tend to use SMALLDATETIME instead of DATETIME, and FLOAT/REAL instead of DECIMAL/NUMERIC, and use the outdated TEXT datatype instead of VARCHAR(8000) or VARCHAR(MAX).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply