Building systems to be dependable is a big issue in today's world of IT, with malice hackers, errors and mischance it becomes increasingly important to build dependable systems that are secure and can adapt. One of the constraints of this project is to use Access 2007 to build an order system the reason for this is that Booths already use Office 2007 and as such building a system using Access will incur no costs for the company. Therefore research has been carried out to look at the advantages and disadvantages of Access and discuss scalability as well as how to create a more secure database.
As a starting point after conducting research detailed below are the advantages and disadvantages
Access: Advantages and Disadvantages:
It is possible to build an application quickly in Access by using Access Wizards to create forms, queries etc but an application can be further tweaked using macros and VBA coding to create more sophisticated software. Ease of use and useful tools create a RAD (rapid application development) environment.
Access has a widely known interface and menus
Access can be integrated with other Microsoft products, meaning that data can be imported from excel and word and exported in the same way.
Access supports industry standards, i.e. relational database: normalization, identity keys, joins and unions etc.
Access can easily work over a network.
Lots of support is available for access including an integrated help system.
Using ODBC drivers, access can communicate with most of the major big-system databases
Access can relatively easily be migrated to Microsoft SQL server.
Access has a size limit of around 2GB and if you exceed that limit the database will seize. However as stated above you can port the back-end data to MS SQL Server.
Although access can work over a network it can only support a small number of users.
Incorporating security within access can be a little tricky and can be limited, if you want good security its best to code your own, however this requires technical ability.
Usually in an organisation each copy of Access has to have its own individual licence which can be costly.
Using graphics within access can be cumbersome unless you have the "know how".
As VBA is an interpreted language it can make your application run slow.
Although access has some issues most of the disadvantages can be overcome, for instance should the application for Booths become over sized it can be easily migrated onto MS SQL Server (a comparison is discussed later). Networking issues will not be an issue for Booths as only 3-4 users will be using the system and they already have licences for all the networked computers using Office 2007. Security measures although a little cumbersome can be incorporated which brings us to the next part of research, security within Access.
The pros and cons have been discussed but as with any application, security is an integral part of software. Fortunately Access can be made more secure by taking the following steps:
File Permissions: Microsoft windows network file permissions can be put onto the file or folder.
Passwords: A single password or multi-level workgroup security can be incorporated to allow different privileges to different users. In Access 2007 the security model is a lot stronger than in earlier versions.
File Encoding: Contents of a database can be encoded at the file level.
Hiding navigation pains such as hiding design UI, navigation shortcuts and category's.
Create a custom menu.
Split the database to create a front and back-end.
It must be stated here that all of the above are designed more for usability than for securing the file. However on a secure network and with only a few users the security should be sufficient, but no matter what you do to Access it will never be as secure as it could be if it was on MS SQL Server. To illustrate the differences a comparison table shows the differences between Access and MS SQL Server
Development environment supports: tables, queries, forms, reports and programming logic
Scalable, reliable and more secure client / server database engine
Maximum Database size
2 gigabytes (GB)
1 terabyte or greater
Maximum concurrent users
Up to 20 concurrent editors
Up to 100 concurrent reports being run
File access-based security
Limited by file share model
Limited only by hardware and application design
Adequate for individuals and small team usage.
Recovery from network failures cannot be rolled back.
High reliability. SQL server is a mission-critical database
Backup and administration tools available.
Chung, L & Haught D (2007)
It is obvious looking at the above table that SQL Server is a high-end solution but it is also a costly one. One of the benefits of Access is that it's a cheaper alternative and should the number of users exceed "the maximum concurrent users" the database can be placed onto SQL Server, meaning access allows for scalability.
Many different approaches can be taken to secure databases but the important aspect is to constantly appraise the security of your database.