August 25, 2008 at 3:43 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 25, 2008 at 4:22 pm
I'd definitely think migrating to SQL Server at least Express edition would be warranted. Access is really not designed to be multi-user back end. I'd think that the Access UI would work fine as well with the SQL Server back end.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 6:51 pm
tcaldw
If memory serves me correctly with Access all necessary data is transmitted to a scratch file on the client, and then the query executes using the scratch file as its data source on the client. Since you are anticipating remote users, this introduces the aspect of very slow response as noted by Microsoft.
From:
http://technet.microsoft.com/en-us/library/cc751805.aspx
Network capacity If multiple users will have to use the application at the same time over a network, data corruption is more likely to occur if the data and logic are combined in one file. Moreover, if you combine the data and the logic in one Office Access 2007 file, you cannot optimize the network traffic that Office Access 2007 generates. If multiple users will simultaneously use your application over a network, you should separate the data and the logic, either by using two or more Office Access 2007 files, or by using a database server product for data and Office Access 2007 for application logic.
So briefly I believe it would be to your companies advantage to do it correctly and move on to SQL Server and write a new interface, or move the data only to SQL server and using the existing Access as the presentation layer while developing the new interface so as to provide an element of continuity to the users while you are working.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply