Visual Basic Programmer

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 organization.

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 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 involve moving a lot of data 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 either a workstation or File-Server fails while a .mdb file is being updated, the Database may be damaged. You can usually recover a damaged Database by compacting and repairing the Database, but you must have all users close Microsoft Access before doing so. The Microsoft Jet Database engine and .mdb files provide transactions, but they are not managed by a separate transaction log. The recovery can fail if the Database file is damaged.

Maintaining a Database on a File-Server is inherently less reliable than using SQL Server. There is also a size limit of 2 Gigabytes.

Microsoft Access Database Project (ADP)

Client/Server solutions use a Client application developed by using a Microsoft Access Database Project file (.adp) that is distributed to each user so that they can access a SQL Server Database from their own computer.

The primary advantage that distinguishes the Client/Server mode of solution development is that all data is under the control of SQL Server.

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.

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.

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 makes it nearly as easy for the Visual Basic Programmer to use SQL Server, as to develop a Microsoft Access Database. The essential idea behind an Access Project is that the .adp file stores only Project or application objects, such as forms and modules, not data – which must reside in an SQL Server Database.

A Microsoft Access Project uses an OLE DB connection to display and work directly with the Tables, Views, Stored Procedures, and Database Diagrams that are stored in an SQL Server Back-end Database. To create the Forms, Reports, and Modules in a Microsoft Access Project, many of the standard tools and wizards are available.

A Microsoft Access Project also provides additional tools that allow the creation of SQL Server objects directly from Microsoft Access. These features allow the rapid development of powerful Client/Server solutions that work directly with the SQL Server Back-end.

Microsoft Access 2007

The design environment of Access 2007 is very different from the previous release. Toolbars and Menus have been replaced by the Ribbon. The Ribbon supports sophisticated control – like the SplitButton. There is a new Layout View which makes designing Forms easier. The Visual Basic IDE is unchanged.

• The first Service Pack for Microsoft Office is now available. Many of the issues with the original release have been resolved - but some bugs of Access 2003 are still present in Access 2007.
• For some unknown reason, Macros are now back in fashion. Macros now respond to Events
• The new Navigation Pane eliminates the need for a Switchboard in simple applications.
• The Ribbon has caused frustration when trying to find routines.
• To use Sendkeys, Microsoft Access 2003 will need to be installed.
• The Help facility has problems.
• For the first time, all MDACs can be found in the References.
• Data Access Pages are no longer supported – use ASP.Net instead.
• Using the Access 2007 Linked Table Manager, the ODBC driver for FoxPro Tables is available. But the driver is not shown in the ODBC Data Source Administrator applet.
• Security messages are a pain. Whenever a Macro (or Function or Procedure) is run, the message "A potential security concern has been identified" appears. To get rid of these messages:
  • Click the Microsoft Office Button
  • Click Access Options
  • Click Trust Center
  • Click Trust Center Settings
  • Click Macros Settings
  • Select "Enable all Macros"

Upgrading using .mdb database files

• Access 2007 accepts the .mdb files from Access 2003, and the upgrade is painless.
• Code derived from Access 97 may not work – like File Dialogs. The code needs to be upgraded.
• Controls derived from Access 97 may not work. The not very helpful message is "There is no object in this control". Copy, then paste the Form. Using the new Form, the message should disappear. Any missing control will need to be replaced with an Access 2007 version.
• You may have to turn off UAC (User Account Control), in order to get some features (like SendKeys) to work.
• Smart Indenter is now standard.

Upgrading to .accdb database files

• There have been many changes (Vista, Office, Access), and one should expect some problems in converting from Access 2003.
• The file size on converting from the old format to the new, is unchanged.
• A new Switchboard is created using Macros. It is better to use the VBA version that is created with mdb databases.
• Online documentation and user comments are sparse.
• If all Forms are displayed as maximised, here is what to do to restore the original size:
  • Click the Microsoft Office Button
  • Click Access Options
  • Click Current Database
  • Find Application Options/Document Window Options
  • Select Overlapping Windows
  • Click OK