Results 1 to 2 of 2
2006-03-27, 18:42 #1
- Join Date
- Aug 2001
- Thanked 0 Times in 0 Posts
SQL Server Back End (Access 2003/SP2)
I am wondering about the advantages/disadvantages of upgrading the back end of a DB I am working with to SQL Server.
The situation is this:
4-6 simultaneous users.
Back End size 8 MB
I am acting as a coach and teacher (as well as developer) to the gal who is working with the db. She created it after taking a couple of 1 day courses at the local community college. She and her boss have repeatedly told me that the main reason they are using this solution is flexibility i.e. he can dream up a report and she can give it to him asap.
I do not anticipate a large increase in users in the next year (perhaps 10 max). They plan on archiving data at the end of each year - so while the db will grow, it isn't going to be infinitely large.
The company doesn't have in-house technical support, so they've hired me and another contractor who maintains their network and servers.
He is all hot to move the back-end into SQL Server.
I'm resisting because I'm wondering if it will interfere with their ease of maintaining the db on their own. I'd be interested in your experiences and feedback.
2006-03-27, 18:58 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: SQL Server Back End (Access 2003/SP2)
8 MB is quite small for an Access database. You won't run into trouble if it grows to many times this size.
If network speed is adequate, Access should be able to handle 4-6 simultaneous users without problems. If this increases to 10, it depends - if all 10 will be entering/editing data simultaneously at full speed, you might see deterioration of performance. If, however, some of those 10 will simply be viewing data (in a form or report), it should be OK.
It is essential that the database is split into a backend containing only tables, and a frontend containing everything else, and to give each user a local copy of the frontend.
At the moment, I don't really see a compelling reason for moving to SQL Server. If the number of users grows to the point where Access becomes too slow, it will be relatively easy to migrate the backend to SQL Server. The only initial change to the frontends would be to change the links to the tables. Later on, you could improve performance by using views in the backend database (SQL Server's equivalent of queries).