How to replace your Access/Excel “Database” with a secure website and phone app

You may be surprised by how many businesses still use Access/Excel to power their businesses. This is fine when the business is small but as it expands it creates real problems in terms of scale-ability and accessibility. We have replaced a number of these systems over the years and this article show the huge benefits of doing this.

Access/Excel “Databases”

A lot of businesses use Access and Excel to capture data when they start trading as they can use these tools to generate simple data capture forms and also generate queries and graphs from this data. This is fine when there are only a few users who are based on the same network but when they company expands you tend to hit the following problems.

• How can people update data at the same time?
• Flaky data validation leading to strange reporting. “Greater Manchester” and “Gtr Manchester” is not the same when reporting!
• Only simple reporting is available in the form of Excel sheets and graphs. Doing Red/Amber/Green trends and drill down reporting can test a lot of users.
• Data cannot be accessed on line or on mobile devices. The worse case scenario is having different versions of Access/Excel files and no one knows which is the correct one.
• Security is very weak, Excel and Access files are very easy to copy.
• Linking to other systems for data transfer/processing is difficult.

By moving to a secure web based system you can overcome all of these problems as well as adding a lot more features to your database system.

Secure Websites and Phone apps

We use the Microsoft .Net MVC framework to create secure mobile friendly websites with all the data stored in a secure SQL Server. To replace an Access/Excel “database” the first task is to import, clean, and optimise the existing data so that it is all stored in the correct format. We then create an SSL REST API web based interface so that all the database transactions go through this. The website is created using C#, Jquery and Angular with page load times of less than one second. The advantages of this system are

• Totally secure REST API interface that can be used by the website, your mobile apps and link to external systems to push and pull data.
• Responsive, mobile friendly, easy to use website.
• Android and Apple mobile apps can be developed so data can be accessed securely by staff while out of the office.
• Microsoft Role based security is built in so users only see relevant content.
• Complete validation carried out on all data entry.
• Users can access the database concurrently.
• Full on-line, automated reporting that can be exported as a csv or pdf file.
• Complete SQL Server backups taken at regular intervals.

As you can see there are a lot of advantages to moving from an Access/Excel “database” system to a secure web based system and in a future article I will detail an actual project that we worked on for a client where we achieved the above.