Extend the life of your Microsoft Access Database
Access databases can be created by the inexperienced user – without any Visual Basic programming experience and with little knowledge of relational databases. From this simple beginning, many a Company administration system evolves.
As the workload increases, the limitations of the design are soon evident. The administration system becomes slow, error ridden, user unfriendly and unmaintainable. In addition, the Access database is likely to have disc space problems.
Find out the extent of the problem
An analysis of the Access database needs to be carried out – database design, structure problems, relationships and throughput.
The Total Access Analyzer software is used to detect errors, provide suggestions, and performance tips. These will help improve Access database design and throughput problems.
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.
Make a strategic plan
The plan needs to be agreed and put into effect, to gradually evolve to a more professional structure. What is required is a database system that can be readily maintained and support the ongoing administration requirements of the company and its users.
The Priorities will be:
- Stabilise the Access Database
- Reduce the size of the Access database
- Convert the Front-End to Visual Basic.Net
- Improve performance and increase user limits
Reducing the size of a Microsoft Access Database
The Microsoft Access Database 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.
A thorough analysis of the Microsoft Access Database will be carried out to determine the main sources of unnecessary Disk space usage.
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 the Front-End Database 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