Networks

Programming

Consulting

Security

Home

Contact us
 
Networks
Programming
Security
Data Recovery
Consulting
Repair
Websites
Training
 
References
About Us

 

When have you outgrown Ms Access ?

 

  For the vast majority of small business or departmental scale users, Access is robust and reliable, but if your application has grown to where it's now running not just your department but your whole company, it may be time to upsize. The easy way to do this is to move the data into SQL Server and keep your investment - by using Access as a "front end" to this data. You can hang on to all your Access  reports and business logic with a bit of work from us.

    When is it time to consider up-scaling to SQL Server ? According to Microsoft at

http://www.microsoft.com/sql/solutions/ssm/access/accessmigration.mspx

and

http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172bc/when_to_Migrate_from_Access.doc

   Access can support a maximum database size of 2 Gig, "with as many as  20 concurrent editors (users updating information ) and 100 users running reports.”

  “ For example, a professionally designed and well-tuned Access application can support up to 20 concurrent users who are updating data with good performance. Databases that are used largely for running read-only reports can scale up to about 100 users”

    “The Jet engine supports up to 255 concurrent users but the performance of the application depends on the design of the application.”  They go on to note that for database size “…this limit is theoretical rather than practical:“ This is due to data being processed locally (with Access) rather than on the server (as with SQL Server )

   There are other issues as well, ones MS call Architectural:

  “Because Access uses the Jet engine for database management, it cannot scale well by definition. Jet is limited to run on a single CPU, whereas client/server solutions such as Microsoft SQL Server can support multiple CPUs.                              

   Additionally, Jet queries always run on the client computer, which eliminates the centralized query or data optimization necessary for a scalable solution.”

 On the issue of data corruption:

  “ Because Access uses a file share model, all users are concurrently holding active connections to data. If any one of those users unexpectedly loses the connection during a data update process, the database may become corrupt" (This has become much less common in the more recent versions of Access. )

 And on the issue of backups:

“Because Access uses the file share model, the entire database is locked at the file level as soon as the first user accesses it. This means that no reliable mechanisms exist for performing backups of the database file unless all users are disconnected.“ (This is not quite true either, as one can just copy the whole .MDB file)

  What to do if you feel you’ve outgrown Access ?

  Microsoft Believes that in 90 percent of the cases Access is just fine, and upsizing is a waste of time and resources. Look, rather, to tuning Access better. We can give you a hand here.

  In 7 percent of the cases they suggest moving only the data over to SQL Server “Because Access can link to SQL Server for table data, migrating only the data is one of the best balances between cost and benefits. In this scenario, you move all table data to SQL Server and leave all forms, reports, queries, macros, and logic in the existing Access database.”

   There are a number of possible scenarios here- Access now supports the project structure in which data is stored in SQL Server files and Access is used as a front end, and Stored Procedures and database diagrams are administered from Access.

  In the other cases MS suggests what amounts to a partial or complete re-write of the application. See the above MS documents for more information.

   We would be happy to assist in any of these cases  Call us for more details.

 

Home

   

Contact us