Visual Basic Programmer

Access Database Programming

Microsoft Access Database features

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 has boosted the productivity of many a company. Microsoft Access 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 any degradation in performance. The Microsoft Access Database 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 Access in File-Server Mode

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.

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 (the Front-End) does all the data processing.

The advantages of a split database architecture are:

• The Back-End database, stored on the Network, allows multiple users to use the Tables. This avoids the problem of sharing the records of a single Access database.
• Performance is improved and network traffic reduced using a local Front-End database, rather than running the Front-End database on the Network.
• Private tables can be kept in the Front-End database. These Tables avoid the overhead of using the Network.
• A split database reduces the chance of crashes and database corruption.
• A split database simplifies Backup administration since the company data is stored in one place on the Network.
• The removal of the Front-End objects allows the Back-End Tables to be expanded to a greater size.
• The split database architecture simplifies the eventual migration to an SQL Server database.

The Disadvantages of File-Server Mode:

• Microsoft Access is not self-tuning. It does not automatically reclaim lost database space, or optimise indexes and queries. This maintenance requires that all users be logged out of the database.
• 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 (if possible).
• The Microsoft Jet Database engine provides transaction management, but this is not stored in a separate transaction log. If either a workstation or the Server fails while a .mdb file is being updated, the Database may be corrupted. This may only be detected some time later. If a corrupted Database cannot be recovered using the Compact and Repair 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.

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 the SQL Server database. The Client/Server architecture provides greater reliability and features that cannot be supplied 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 with Microsoft Access Project (ADP). 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.

It is best to use Access tables linked to the SQL Server and ADO with OLEDB, rather than using ADP.

Microsoft Access and Microsoft SharePoint:

Microsoft SharePoint is basically a Web site that allows sharing of data across a network. It must reside on Windows Server 2003 or SharePoint 2010. SharePoint is usually used to share information in a company, using an intranet on a Local Area Network (LAN). SharePoint is not intended for large-scale Internet use.

All data, such as document libraries and lists, are stored in an SQL database. SharePoint is well integrated with Access 2007. The SharePoint data can be viewed from Access as a Linked Table, or viewed with Internet Explorer. The Forms and Reports of Microsoft Access can use the SharePoint data.

SharePoint is still in its infancy, so it will be wise to wait for a few Service Packs and developer feedback.