Views and Queries
Due to the database having been created in an ADP format, a considerable number of queries and database operations were coded in the front end, which then were sent to the SQL database to be run as a Stored Procedure. Where appropriate these were recoded to run directly from the front end or to be called directly from the SQL Server without the requirement to be passed into a stored procedure first.
Several of the old Forms had multiple calculated fields (over 30 in one form alone) which would impact performance, as these fields are calculated when the form is opened, or records are changed. Many of these calculations were transformed into a SQL View and linked to the data table instead. This meant the data was ready to hand when the form opened, avoiding a costly calculation.
Reporting
Reporting was built from scratch, not only providing a more uniform method to run reports but also increasing the options available. All reports are available from a dedicated reporting menu, allow filters to be selected, and can be presented in a native Access report, as an Excel workbook, or as a PDF document. Due to the way the data is now stored and linked; additional reports were also built which could not have been built in the previous version without a considerable amount of work. With an eye to future reporting, the whole reporting functionality has been built using a generic form and VBA code which can adapt to any additional filter requirements.
Importing Data
It was requested that data can be imported from a spreadsheet instead of having to manually enter the data line by line. This posed several challenges including:
- Being able to read the Spreadsheet data and then check the data in the spreadsheet had no errors.
- Load the data into a temporary dataset/table so that it can be manually checked and corrected where necessary.
- To match up records in the spreadsheet with a record stored in a database.
- To either insert a new record or update a current record in the database.
Administration Functionality
The new database was built with additional security features including the ability to limit each form to either be inaccessible, read only, or fully read/write depending on the user who is logged in. Additional roles can also be created to allow specific access to specific forms (for example a “Reporting” role was created to only allow access to the reports).
Hardcoded values in the old database (for example tax limits) were now moved into a table, allowing the user (with a specific permission) to be able to adjust these at any time in the future should they change.
Smart Fields
One important request was that some of the fields should become smarter, providing pre-defined values depending on other selections or displaying warnings if incorrect data has been entered. Examples of this include:
- Date field providing suggested dates based on other date field values.
- Checking dates fields have been entered correctly.
- Checking if a field is a unique value within a group of other values.
- Recalculating days between two dates entered.
Indexing and Normalisation
Indexing is where a unique key value (normally numeric) is assigned to a record to be able to identify each record.
Normalisation is where we replace field values which are often repeated (for example, the words “male” and “female” in the Gender column) with a numerical index which is stored on a separate table along with the word the index replaced (and possible additional information).
Both these methods were not fully utilised in the old database, meaning any searching, reporting, and data manipulation could result in a slower than necessary performance. The new database was designed with all tables indexed and normalised.
This results in a more efficient database design, quicker performance, and can reduce data entry mistakes as the user can now enter data from a list of options, rather than having to type in a value fully.
Contact us to find out more about our Access Database development and support.
Contact
