Why Use an Access Database Library?

Maximizing code re-use should be the goal of the professional programmer. All too frequently the same code is repeated in numerous forms and procedures. And the same code is often repeated in many other Access databases.

From the developer’s point of view, using less code means greater productivity and faster development cycles. Forms, Reports, and procedures can be built faster – and will run faster.

The best way to remove repeated code in a Microsoft Access database is through the use of an ACCDE Library. The ACCDE file is a compiled and executable version of an Access database file. It does not allow the user to read or modify the Visual Basic source code.

The ACCDE file should be regarded as the equivalent of the DLL file – without the complexity.

Why an ACCDE Library is a necessity

There are many reasons why an ACCDE Library should be used to remove redundant code:

• Improves performance – the application loads and runs faster
• Optimizes memory usage – the application cannot become uncompiled and cause database bloat
• Efficiency – memory is better utilized with code re-use
• Security – intellectual property is protected
• A common resource – the ACCDE may be shared between different database projects
• Stability – the Front-End database becomes more robust and reliable
• Easier maintenance – due to a smaller Front-End database code size and reduced complexity
• Front-End Access limits – less likely to be reached
• End-user productivity – less training is needed with standardized and consistent software routines
• Crashes – errors are less likely to occur with shared and re-usable code
• Change management – simplified as only one Library modification needed

When a database is saved as an ACCDE file, Access compiles all the code modules including Reports and Forms, removes all editable source code and compacts the database. The resulting ACCDE Library file is fast, memory-efficient and small.

Hopefully the above will convince you that an Access Library of re-usable procedures is essential.

Eliminate Repeated Code

Start by searching the Access Front-End for repeated code. Likely candidates are modules with:

• Error Handling
• ADO and DAO database retrieval and updating
• Microsoft Word functions
• File Handling
• Consistent ActiveX Control colors
• Security control
• Validation and Formatting

Where variations in code procedures are found, select the best or handle the variation with Optional Arguments. This makes for powerful functionality.

Setting up a Library Database

This is easy to do in Access 2010:

• Create a blank ACCDB Access database
• Add code modules
• Compile
• Save the ACCDB file
• Create an ACCDE from the File, Save & Publish Menu.

Some suggestions

• Prefix all the Library Modules with “lib”, so that the Modules can be easily distinguished.
• It is possible to deploy an Access database to the users as an ACCDE file.
• The ACCDE Library must reside in the same folder path in both the development and user environments. Alternatively, the Reference folder path must be set in Visual Basic code at start-up.
• Make sure that each Module has error handling on all procedures, and that all errors are logged to a central folder.

Source by Neville Silverman