Our client had developed an Access database to manage their depot. The database worked well as a single user application but there were problems when other users were added to the system:
- The system was slow and transactions frequently failed due to communication issues between depots and head office.
- The database had been created for a single site.
- The client had some specific coding questions.
Nepeta Consulting were asked to provide consultancy to solve these issues.
Move to SQL Server
The first recommendation was to use SQL Server as the back end database. This provides considerable benefits over Access as a back end database:
- SQL Server is not as sensitive to slow communications between sites and manages transactions better.
- Backups can be managed and are not dependent on whether the file is open at the time.
- Security can be managed through Active Directory.
- SQL server manages multiple users more robustly than Access.
While there is a macro that will convert an Access database into SQL Server, we prefer a more manual approach. This way, we can make sure that all the fields are defined appropriately and the data converted correctly. This picks up any data integrity problems and reduces the likelihood of problems in the future.
One into seven does go!
In order to use the system for multiple depots, we had to modify the database design and front end forms and reports:
- Adding a depot key to tables which hold depot specific data.
- Working out what data and reports should be visible to different users within the business.
- Setting up a permissions structure to control access by depot and also by role.
- Updating the interface to provide screens appropriate to the logged on user.
- Filtering data displayed to that allowed for the logged on user.
The job of implementing the changes was shared between the client and ourselves. It was important for the client to understand their database so they could use and maintain it going forward.
Managing time
One of the coding issues was how to hold historic information as well as current data. Giving records a start and end date is just part of the solution. You then have to make sure all your queries are picking up the correct record. We introduced a calendar table and showed the client how it was used. This simplified many of the queries used to produce financial and management reports. We included week and year numbers in the table and this has made the accounting process much simpler.
Handover
Once we had tested all the changes thoroughly, we created and populated the live system. The client managed a phased implementation across the different depots. We are available to assist when we are needed.
