DATABASE CHOICES
Microsoft Access
The Microsoft Access Database system is the most popular desktop relational Database
system in the world today. The Microsoft Access Database is significantly cheaper
to implement and maintain compared to using larger Database systems.
The Microsoft Access Database system is ideal for the small business or company
department. It is a powerful and versatile tool, and when combined with Microsoft
Visual Basic for Applications (VBA) – there are very few small business systems
that cannot be programmed.
- Microsoft Access allows simple or complex graphing presentations.
- Microsoft Access has extensive report writing functionality.
- Microsoft Access has a database Query facility.
- Microsoft Access has a Wizard to create data bound grids.
- Microsoft Access has powerful Visual Basic programming procedures
The Microsoft Access Database system has more support and consultants available
than any other Database system. The Consulting rates are considerably lower compared
to Oracle or SQL Server Databases. Microsoft Access enables a complex database to
be set up and running in at least one half the time and cost of other Database systems.
Microsoft Access Database custom software development ensures that Microsoft Visual
Basic Programming solution is unique to your organisation.
The Microsoft Access Database can be optimised for large record sets, storing many
thousands of records without experiencing degradation in performance. The Microsoft
Access Database also performs well on networks with up to 10 simultaneous users.
The full power of Microsoft Access can only be realised with the use of the Microsoft
Visual Basic Programming language. In addition to working with data and objects
such as forms and reports in the Microsoft Access interface, Microsoft Visual Basic
is used to write procedures that dynamically create, delete, and modify the Microsoft
Access data. Microsoft ActiveX Data Objects (ADO) is used with Microsoft Visual
Basic to connect to, retrieve, manipulate, and update the Microsoft Access data
and the database structure. ADO provides the objects, such as tables, queries, relationships,
indexes, that handle data-management tasks in the Microsoft Access database.
Microsoft Access and the Jet Engine
Microsoft Access has its own database engine – the Microsoft Jet Database
Engine. Jet is designed as a file share database that supports single and multi-user
database applications with databases up to 2 GB in size.
Microsoft Access is not self-tuning. It does not automatically reclaim lost database
space, or optimise indexes and queries. This maintenance can be performed by running
the repair/compact utility, but it requires that all users be logged out of the
database.
Microsoft Access in File-Server Mode
In order to support multiple users, the data portion of a Microsoft Access Database
(the Back-end) is stored on a Server. The Jet Database engine that runs on the Client
workstation does all the data processing. Each user's machine retrieves the physical
pages over the network, updates them, and writes them back to the Server. For large
Databases, this can cause excessive traffic over the network.
The maximum number of users on a File-Server system depends upon the network traffic.
As the user numbers increase:
- Excessive network traffic will be generated by the requests being sent from each
user's Jet Database engine.
- The probability of a failure increases.
- A failure on any Client machine has a good chance of corrupting the Database and
requiring a shutdown and repair.
The Microsoft Jet Database engine and .mdb files provide transactions, but they
are not managed by a separate transaction log. If either a workstation or the File-Server
fails while a .mdb file is being updated, the Database may be corrupted. If a corrupted
Database cannot be recovered using the Compact and Repairing utility, this will
result in data loss and business disruption.
Microsoft Access uses the file share model, so the entire database is locked at
the file level as soon as it is accessed by the first user. This means that there
are no reliable mechanisms for performing backups of the database file unless all
users are disconnected. Often, this is only detected some time after the backup
fails.
Maintaining a Database on a File-Server is inherently less reliable than using SQL
Server.
Microsoft Access in Client/Server Mode
The primary advantage that distinguishes the Client/Server mode of solution development
is that all data is under the control of SQL Server. By isolating all Database files
under the control of SQL Server, the Client/Server architecture can provide greater
reliability and features that cannot be furnished by the File-Server architecture.
The decision to use a Client/Server solution is driven primarily by two factors:
scalability and reliability. When user numbers increase, a Client/Server solution
should be considered instead of a File-Server solution.
Microsoft Access and Microsoft SQL Server are the ideal combination for developers
building custom business solutions. Microsoft Access lets developers integrate SQL
Server Databases into custom Microsoft Access applications that can easily be deployed
across an office.
An Access Project (ADP) supports the direct creation and editing of SQL Server tables,
views, and stored procedures. But ADP cannot store local tables or link to data
from other sources. Microsoft recommends the use of linked tables in Access for
getting to SQL Server data rather than using ADPs.
|