CSL has built many Microsoft Access database apps in the late 90s and eary 2000s, we thereafter converted many Access based applications to .Net and PHP Cloud apps or off the shelf software systems. Our experience in building Access based applications has been instrumental in helping us understand existing Access applications and convert them effectively.

Are you currently using an MS Access Database and considering the possibility of transforming it into a web-based application? Let’s delve into some important points about MS Access and explore the benefits of web-based applications over platform-specific ones like MS Access.

Access is most suitable for a relatively small number of concurrent users and is limited to local networks. This means that if your database needs to accommodate many users or be accessed remotely, MS Access might not be the most optimal choice. Additionally, its performance tends to slow down with larger data sets. As your database grows in size and complexity, MS Access may struggle to handle the increased workload efficiently. Moreover, scalability can be an issue with MS Access. If your application demands the ability to scale up and handle a growing number of users or data, you might encounter limitations with MS Access.

A major challenge with MS Access is its locking issues involving multiple users. Due to its design and architecture, MS Access can face difficulties with concurrent access by multiple users. This can lead to conflicts and delays in data updates, affecting the overall performance and user experience.

A common concern is a risk associated with relying on MS Access as a mission-critical application when the employee responsible for maintaining it is retiring or leaving the company. If no one else is familiar with the intricacies and internals of the application, it can become a significant challenge to ensure its continued operation and support.

MS Access is not the most efficient option for remote work scenarios. While it is possible to access an MS Access application remotely using tools like Remote Desktop Connection, the experience may not be as smooth and optimized as using a web-based application. Web-based applications offer instant availability, allowing users to access them from any device with a browser and an internet connection. This flexibility makes web-based applications more suitable for remote work setups, where users may access the application from various locations and devices.

Despite these limitations, it’s worth noting that MS Access can be a convenient tool for certain use cases. It provides a user-friendly interface for building small-scale databases and applications. For small teams or individual users with relatively simple database needs, MS Access can offer a quick and accessible solution. However, transitioning to a web-based application becomes a compelling option as business requirements evolve and expand.

There are 2 common choices available to companies trying to move away from MS Access.

1. Off the shelf software like Zoho, SalesForce, SugarCRM, and other customizable CRM solutions

2. Web application using .Net or PHP build to mimic MS Access functions. Deployed on the Cloud and accessible via computers and smart devices.

The first step at CSL is to guide you toward the option that would work best for you regarding function, costs, and management.

Assuming your process cannot be replicated in an off-the-shelf program. Here are the general steps involved in the web application conversion process:

Build a modern storyboard1. Build a modern storyboard that includes all the forms and reports that the currently in use.

In our experience, many features are often unused and can be deprecated at this stage to simplify the project. The new UI would be easier to work with, faster in performance and would support all smart devices.

Import or migrate the data from MS Access tables 2. Import or migrate the data from MS Access tables to a more robust database management system (DBMS) like MS SQL Server, MySQL, or any other RDBMS you choose.
This step is crucial as it lays the foundation for the web-based application. Migrating the data involves transferring the tables, relationships, and data from MS Access to the new RDBMS. One popular option is to use SQL Server as the backend database. The free SQL Server Express edition is often sufficient, which provides ample capabilities for small to medium-sized applications.

Microsoft provides a helpful tool called SQL Server Migration Assistant for Access to assist with this migration process. This tool simplifies the task of migrating your data from MS Access to SQL Server, automating much of the process. It analyzes your Access database and helps you map the tables, columns, and data types to the corresponding objects in SQL Server.

Recreate queries as views 3. Recreate queries as views.
In MS Access, queries are commonly used to retrieve and manipulate data. However, when migrating to a web-based application, the Migration Assistant might not automatically convert all types of queries. The tool primarily focuses on converting SELECT queries, but queries that involve parameters from linked forms or human entries may require manual re-creation. Similarly, INSERT, UPDATE, or DELETE queries might need to be recreated from scratch to fit the new environment.
Recreate forms 4. Recreate forms.
Once the data is successfully migrated to the new DBMS, the next step is to recreate the forms in the web-based application. Forms in MS Access provide the user interface for data entry, display, and interaction. However, in a web-based application, the forms need to be reimagined using web technologies and programming languages. The specific programming language and framework you choose will depend on your preferences and the requirements of your application.

Common choices for web development include ASP.NET, PHP, Node.js, Java, Python, and many others. These languages offer frameworks and libraries that facilitate the creation of web-based user interfaces. Modern UI prototyping tools can also aid in recreating the forms’ appearance and layout. However, it’s important to keep in mind that forms in MS Access often contain business logic implemented using VB code or SQL queries with parameters. This logic will need to be re-implemented in the chosen programming language to ensure the functionality of the forms in the web-based application.

Recreate reports 5. Recreate reports.
Reports in MS Access are used to present data in a formatted and printable manner. Similar to forms, reports will need to be recreated in the web-based application. Simple reports based on a single query can be relatively straightforward to recreate using existing tools or libraries available in your chosen programming language. However, more complex reports with multiple data sources, grouping, or calculations may require building them from scratch. It’s important to assess your reporting requirements and identify the tools or libraries that can best support the generation of reports in the web-based application.

Additionally, if you need to export reports to formats such as Excel or PDF, you must utilize appropriate libraries or APIs to generate those formats programmatically.

Rewrite custom VB code6. Rewrite custom VB code.
MS Access uses Visual Basic (VB) as its programming language, allowing users to write custom code to implement specific functionalities. When converting to a web-based application, the existing VB code in MS Access will need to be rewritten in the chosen programming language for the web. This step involves understanding the functionality implemented in the VB code and translating it into the equivalent code using the syntax and conventions of the new programming language.

It’s essential to thoroughly review the existing VB code and ensure that all necessary functionalities are translated accurately. Additionally, don’t forget that MS Access can contain code in macros and modules that are not directly tied to specific forms or reports. Such code snippets may need to be examined and integrated into the appropriate sections of the web-based application.

Setup cloud-based backups7. Setup cloud-based backups, periodic security scans, and logging for important areas of the platform for long-term seamless operation.
Converting an MS Access database to a web-based application requires careful planning, attention to detail, and technical expertise in both database management and web development. Each step of the conversion process presents its own challenges, and there may be specific considerations based on the complexity and size of your database.

No automatic converters are available that can handle the entire migration process. However, CSL can take on the complete process of migrating and retiring MS Access applications. We provide comprehensive solutions to ensure a smooth transition from MS Access to a web-based application.

If you decide to work with CSL for your Access conversion project, you can expect to receive a SQL Server-backed database, with the option to use MySQL as an alternative. The database will provide a robust and scalable foundation for your web application, accommodating the needs of multiple users and large datasets. The web-based application itself will be built using modern technologies, ASP.NET or PHP, offering a responsive and user-friendly interface accessible from various devices, including smartphones, tablets, and desktops.

Security is of utmost importance; we ensure strong and secure user authentication, including single sign-on functionality. Additionally, flexible access rights can be implemented, allowing you to control user permissions and restrictions based on their roles and responsibilities within the application.

Efficient user management is another key feature. Regardless of the number of users, the system will offer easy administration and user onboarding, simplifying the process of managing user accounts and access control.

All your existing forms and reports will be recreated during the migration into the new web-based application, ensuring continuity and familiarity for your users. Furthermore, you will receive the full source code of the web application, allowing you to customize and maintain it in-house or host it on your own cloud infrastructure. Cloud platforms such as AWS or Azure readily host converted Access applications relieving you from managing in-house servers and backup infrastructure.

Moreover, if you have multiple Access databases, these services can consolidate them into a cohesive web application, streamlining your operations and enhancing data management efficiency.

Contact us today, and our team of web developers will assist you in transforming your MS Access database into a modern, web-based application that empowers your business with enhanced functionality, accessibility, and efficiency.

Case Study

CBE

The Combined Book Exhibit Inc

Shows Registration and Catalog Management

CBE, with its extensive database of publishers and publications, relied on an Access database-driven application for years. However, as the application started impeding employee productivity and hindering business growth, CSL stepped in. We developed a customized system that matches the performance of leading off-the-shelf solutions and precisely fulfills their requirements. Our system is both versatile and user-friendly, empowering CBE with enhanced functionality and ease of operation.

Dera Food

Dera Foods Technology

CRM and Project Management system – PDA Enabled !

Dera Food Technology, a multinational company operating in over 10 countries, sought a solution to replace its outdated Access database. Their requirement was a system that offers seamless accessibility via the Internet and PDAs. To address their needs, we converted their MS access to a robust web application that provides full internet access and supports mobile devices.