Extend the life of your Microsoft Access Database
The biggest limitation of a Microsoft Access Database is the file size. The disk size is limited to a maximum of 2 gigabytes.
This maximum is generally sufficient for most small to medium sized companies. However, after a few years of accumulating data, many companies find the disk file size limitation a problem.
The next issue encountered is the limited number of users that can use an Access Database before performance becomes unacceptable.
Access Database systems can be readily optimised
Instead of drastic and costly measures being taken, like a conversion to SQL Server or the purchase of third-party software, the Access Database can be optimised and its life extended.
This will be done in a cost-effective, disciplined and controlled manner.
Reducing the size of a Microsoft Access Database
A thorough analysis of the Microsoft Access Database will be carried to determine the main sources of remedial Disk space usage.
The Disk space issues that will be addressed:
- Temporary or infrequently changed Tables moved from the Back-End Database
- The Back-End Database split into multiple Databases
- Historic records regularly deleted
- Necessary Historic records stored in a separate Database
- BLOBs (Word Documents, Images, etc) stored in Windows Folders
- Memo fields stored as Windows Text files
- Text fields stored in Unicode 2-byte character format compressed
- The Database regularly Compressed and Repaired
- The Database reconstructed from scratch
- The smallest data type (i.e. Integer from Long Integer) chosen
Convert the Front-End database to Visual Basic.
The next issue that usually causes problems for a growing company, is the limited number of users before response times become unacceptable.
A conversion of the Front-End Access database to Visual Basic will improve response times and increase the number of possible simultaneous users. The conversion can be done in stages – whilst still maintaining the Front-End Database, until it can be discarded.
The Visual Basic Front-End will use:
- Modern communication techniques – to reduce Access database overhead
- Modern Forms and Controls – making the users as productive as possible
- A Multiple Document Interface (like Microsoft Word) – to view records
- A Tree-View Menu (like Windows File explorer) – for user-friendly item selection
- A Library of tried and tested procedures – to greatly reduce development times
Using Visual Basic, all the inefficiencies and problems relating to Access Forms (and they are many) will be eliminated.
For an example of the conversion process, see The Visual Basic Conversion Process