Why use an Access Database Library?
Maximising code re-use should be the goal of the professional programmer. All too frequently the same code is repeated in numerous forms and procedures.
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 Database 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 Library should be regarded as the equivalent of the Class Library of VB.Net, without the complexity of using a DLL.
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
- Optimises memory usage - the application cannot become uncompiled and cause database bloat
- Efficiency - memory is better utilised 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 standardised 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 in the Visual Basic procedures
Start by searching the Access database 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 colours
- 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 an Access Library Database
This is easy to do with ACCDB databases:
- Create a blank ACCDB Access database
- Add code modules
- Save the ACCDB file
- Create an ACCDE from the File, Save & Publish Menu.
Access Modules: 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 file in a common folder.