Microsoft Access Developer Challenges When Inheriting Databases
I recently visited a prospect client to assess and evaluate a mix of Access databases and Excel spreadsheet files which constituted in the company side operational database system only to discover as a Microsoft Access developer my worst fears of:
- More than one sensible database application was being used.
- There were different instances stored locally with very little workgroup control.
- Excel files were acting as a relational database files (very badly).
- No documentation to be seen at all.
- Even had two different versions of MS Access (2003 and 2010) running.
I didn’t even need to know much of the history, development cycle or background here as I already knew most of the answers including that fact over time there had been two or more developers (or more accurately; Access power-users) who had arrived and then disappeared leaving their foot-print in an organisation with no structure or workflows in place.
So where do you start?
The simple and quick answer would be to carry out an ‘audit’ of the current systems and gather as much information as possible to propose a plan going forward.
Microsoft Access Developer Challenges – Taking Over A Project
Before I even consider this project, the reasons why many organisations have a mixture or systems within the Microsoft realm is because most desktop PC’s ship with MS Office (pro – which includes Access) and it’s an obvious choice since it’s already licenced and paid for. But as a Microsoft Access developer, I would never suggest using this application unless the workflow and other key factors justified it and would need to carry out a ‘needs and gap analysis’ to help determine which route to take.
The other element that crosses my mind very early on is if the Microsoft desktop application route is the determined outcome whether valid or by the insistence of the client, then do we start from a blank canvas or consider migrating to a new development into existing systems?
So here are some points which can be documented as questions too as the preliminary report before engaging into real action to help evaluate the business needs:
- Who will be responsible and take over the database systems pre and post development?
- Does the prospect client has the correct skills to self-support any highly developed Access databases including writing queries, SQL, Access VBA programming code and general administrative tasks?
- How will the end-users share information and keep synchronised data sets well, synchronised – LAN, WAN or some other network collaboration?
- Allowing end-user design functionality from a simple query to a basic report and keeping other objects locked and protected which means designing the extra interfaces (forms) to help handle workflows.
- Will there be a need to provide technical documents for the system’s design and also provide end-user guide documentation and company workflow practices too?
- Data migration is a big challenge for Microsoft Access developers in deciding how to migrate and populate existing data into any new database structures or starting from a blank point and retain the old data for historical reporting reasons.
- Do you need to provide a phased development plan due to the large project potential and to stop data bleeding and inaccurate processes failing the organisation?
- What are the back-up and restore strategies that any new system will be merged with?
- Who is going to do the testing and become part of the development team during and after the database build?
- What about training? Some end-users will probably need basic MS Access training in the general day to day use for custom built application. Others will need to know more about building queries and reports; More advanced and authorised users will need to know the database architecture and administration tools around to manage any low level supporting issues.
For me, there are more technical challenges to consider when inheriting an Access database and include the like of needing to split databases into back-end and front-end systems, making sure VBA code is compiled and error handlers have been included, coding is also easy to follow and modular, and that database architecture is solid using the Access normalisation process keeping data easy to manage and optimised (performance enhanced).
I have been an Microsoft Access developer for many years now and if you want help in training, consultancy or development, then please feel free to get in contact with me to discuss your requirements further.