How to Modernise a Microsoft Access Database (Forms + VBA) to Node.JS, OpenAPI and SQL Server

Microsoft Access has played a significant role in enterprise environments for over three decades. Released in November 1992, its flexibility and ease of use made it a popular choice for organizations of all sizes—from FTSE250 companies to startups and the public sector. The platform enables rapid development of graphical user interfaces (GUIs) paired with relational databases, allowing users to quickly create professional-looking applications. 

Developers, data architects, and power users have all leveraged Microsoft Access to address various enterprise challenges. Its integration with Microsoft Visual Basic for Applications (VBA), an object-based programming language, ensured that Access solutions often became central to business operations. Unsurprisingly, modernizing these applications is a common requirement in contemporary IT engagements as thse solutions lead to data fragmentation, lack of integration into master data systems, multiple copies of the same data replicated across each access database and so on. 

At first glance, upgrading a Microsoft Access application may seem simple, given its reliance on forms, VBA code, queries, and tables. However, substantial complexity often lurks beneath this straightforward exterior. Modernization efforts must consider whether to retain the familiar user interface to reduce staff retraining, how to accurately re-implement business logic, strategies for seamless data migration, and whether to introduce an API layer for data access. These factors can significantly increase the scope and effort required to deliver a modern equivalent, especially when dealing with numerous web forms, making manual rewrites a daunting task. 

This is where GitHub Copilot can have a transformative impact, dramatically reducing redevelopment time. By following a defined migration path, it is possible to deliver a modernized solution in as little as two weeks. 

In this blog post, I’ll walk you through each tier of the application and give you example prompts used at each stage. 

🏛️Architecture Breakdown: The N-Tier Approach 

Breaking down the application architecture reveals a classic N-Tier structure, consisting of a presentation layer, business logic layer, data access layer, and data management layer. 

💫First-Layer Migration: Migrating a Microsoft Access Database to SQL Server 

The migration process began with the database layer, which is typically the most straightforward to move from Access to another relational database management system (RDBMS). In this case, SQL Server was selected to leverage the SQL Server Migration Assistant (SSMA) for Microsoft Access—a free tool from Microsoft that streamlines database migration to SQL Server, Azure SQL Database, or Azure SQL Database Managed Instance (SQLMI). While GitHub Copilot could generate new database schemas and insert scripts, the availability of a specialized tool made the process more efficient. 

Using SSMA, the database was migrated to SQL Server with minimal effort. However, it is important to note that relationships in Microsoft Access may lack explicit names. In such cases, SSMA appends a GUID or uses one entirely to create unique foreign key names, which can result in confusing relationship names post-migration. Fortunately, GitHub Copilot can batch-rename these relationships in the generated SQL scripts, applying more meaningful naming conventions. By dropping and recreating the constraints, relationships become easier to understand and maintain. 

SSMA handles the bulk of the migration workload, allowing you to quickly obtain a fully functional SQL Server database containing all original data. In practice, renaming and recreating constraints often takes longer than the data migration itself. 

Prompt Used: 
# Context 

I want to refactor the #file:script.sql SQL script. Your task is to follow the below steps to analyse it and refactor it according to the specified rules. 

You are allowed to create / run any python scripts or terminal commands to assist in the analysis and refactoring process. 

# Analysis Phase 

Identify: 

1.  Any warning comments

2.  Relations between tables

3.  Foreign key creation

4.  References to these foreign keys in 'MS_SSMA_SOURCE' metadata

# Refactor Phase 

Refactor any SQL matching the following rules: 

  - Create a new script file with the same name as the original but with a `.refactored.sql` extension 

  - Rename any primary key constraints to follow the format PK_{table_name}_{column_name} 

  - Rename any foreign key constraints like [TableName]${GUID} to FK_{child_table}_{parent_table} 

  - Rename any indexes like [TableName]${GUID} to IDX_{table_name}_{column_name} 

  - Ensure any updated foreign keys are updated elsewhere in the script 

  - Identify which warnings flagged by the migration assistant need addressed 

# Summary Phase 

Create a summary file in markdown format with the following sections: 

  - Summary of changes made 

  - List of warnings addressed 

  - List of foreign keys renamed 

  - Any other relevant notes 

🤖Bonus: Introduce Database Automation and Change Management 

As we now had a SQL database, we needed to consider how we would roll out changes to the database and we could introduce a formal tool to cater for this within the solution which was Liquibase. 

Prompt Used: 
# Context 

I want to refactor #file:db.changelog.xml. Your task is to follow the below steps to analyse it and refactor it according to the specified rules. 

You are allowed to create / run any python scripts or terminal commands to assist in the analysis and refactoring process. 

# Analysis Phase 

1.  Analyse the generated changelog to identify the structure and content.

2.  Identify the tables, columns, data types, constraints, and relationships present in the database.

3.  Identify any default values, indexes, and foreign keys that need to be included in the changelog.

4.  Identify any vendor specific data types / fucntions that need to be converted to common Liquibase types.

# Refactor Phase 

DO NOT modify the original #file:db.changelog.xml file in any way. Instead, create a new changelog file called `db.changelog-1-0.xml` to store the refactored changesets. The new file should follow the structure and conventions of Liquibase changelogs. 

You can fetch https://docs.liquibase.com/concepts/data-type-handling.html to get available Liquibase types and their mappings across RDBMS implementations. 

1.  Copy the original changesets from the `db.changelog.xml` file into the new file

2.  Refactor the changesets according to the following rules:

  - The main changelog should only include child changelogs and not directly run migration operations 

  - Child changelogs should follow the convention db.changelog-{version}.xml and start at 1-0 

  - Ensure data types are converted to common Liquibase data types. For example: 

    - `nvarchar(max)` should be converted to `TEXT` 

    - `datetime2` should be converted to `TIMESTAMP` 

    - `bit` should be converted to `BOOLEAN` 

  - Ensure any default values are retained but ensure that they are compatible with the liquibase data type for the column. 

  - Use standard SQL functions like `CURRENT_TIMESTAMP` instead of vendor-specific functions. 

  - Only use vendor specific data types or functions if they are necessary and cannot be converted to common Liquibase types. These must be documented in the changelog and summary. 

3.  Ensure that the original changeset IDs are preserved for traceability.

4.  Ensure that the author of all changesets is "liquibase (generated)"

# Validation Phase 

1.  Validate the new changelog file against the original #file:db.changelog.xml to ensure that all changesets are correctly refactored and that the structure is maintained.

2.  Confirm no additional changesets are added that were not present in the original changelog.

# Finalisation Phase 

1.  Provide a summary of the changes made in the new changelog file.

2.  Document any vendor specific data types or functions that were used and why they could not be converted to common Liquibase types.

3.  Ensure the main changelog file (`db.changelog.xml`) is updated to include the new child changelog file (`db.changelog-1-0.xml`).

🤖Bonus: Synthetic Data Generation 

Since the legacy system lacked synthetic data for development or testing, GitHub Copilot was used to generate fake seed data. Care was taken to ensure all generated data was clearly fictional—using placeholders like ‘Fake Name’ and ‘Fake Town’—to avoid any confusion with real-world information. This step greatly improved the maintainability of the project, enabling developers to test features without handling sensitive or real data. 

💫Second-Layer Migration: OpenAPI Specifications 

With data migration complete, the focus shifted to implementing an API-driven approach for data retrieval. Adopting modern standards, OpenAPI specifications were used to define new RESTful APIs for creating, reading, updating, and deleting data. Because these APIs mapped directly to underlying entities, GitHub Copilot efficiently generated the required endpoints and services in Node.js, utilizing a repository pattern. This approach not only provided robust APIs but also included comprehensive self-describing documentation, validation at the API boundary, automatic error handling, and safeguards against invalid data reaching business logic or database layers. 

💫Third-Layer Migration: Business Logic 

The business logic, originally authored in VBA, was generally straightforward. GitHub Copilot translated this logic into its Node.js equivalent and created corresponding tests for each method. These tests were developed directly from the code, adding a layer of quality assurance that was absent in the original Access solution. The result was a set of domain services mirroring the functionality of their VBA predecessors, successfully completing the migration of the third layer. 

At this stage, the project had a new database, a fresh API tier, and updated business logic, all conforming to the latest organizational standards. The final major component was the user interface, an area where advances in GitHub Copilot’s capabilities became especially evident. 

💫Fourth Layer: User Interface 

The modernization of the Access Forms user interface posed unique challenges. To minimize retraining requirements, the new system needed to retain as much of the original layout as possible, ensuring familiar placement of buttons, dropdowns, and other controls. At the same time, it was necessary to meet new accessibility standards and best practices. 

Some Access forms were complex, spanning multiple tabs and containing numerous controls. Manually describing each interface for redevelopment would have been time-consuming. Fortunately, newer versions of GitHub Copilot support image-based prompts, allowing screenshots of Access Forms to serve as context. Using these screenshots, Copilot generated Government Digital Service Views that closely mirrored the original application while incorporating required accessibility features, such as descriptive labels and field selectors. 

Although the automatically generated UI might not fully comply with all current accessibility standards, prompts referencing WCAG guidelines helped guide Copilot’s improvements. The generated interfaces provided a strong starting point for UX engineers to further refine accessibility and user experience to meet organizational requirements. 

🤖Bonus: User Story Generation from the User Interface 

For organizations seeking a specification-driven development approach, GitHub Copilot can convert screenshots and business logic into user stories following the “As a … I want to … So that …” format. While not flawless, this capability is invaluable for systems lacking formal requirements, giving business analysts a foundation to build upon in future iterations. 

🤖Bonus: Introducing MongoDB 

Towards the end of the modernization engagement, there was interest in demonstrating migration from SQL Server to MongoDB. GitHub Copilot can facilitate this migration, provided it is given adequate context. As with all NoSQL databases, the design should be based on application data access patterns—typically reading and writing related data together. Copilot’s ability to automate this process depends on a comprehensive understanding of the application’s data relationships and patterns. 

# Context 

The `<business_entity>` entity from the existing system needs to be added to the MongoDB schema. You have been provided with the following: 

- #file:documentation - System documentation to provide domain / business entity context 

- #file:db.changelog.xml - Liquibase changelog for SQL context 

- #file:mongo-erd.md - Contains the current Mongo schema Mermaid ERD. Create this if it does not exist. 

- #file:stories - Contains the user stories that will the system will be built around 

# Analysis Phase 

1.  Analyse the available documentation and changelog to identify the structure, relationships, and business context of the `<business_entity>`.

2.  Identify:

  - All relevant data fields and attributes 

  - Relationships with other entities 

  - Any specific data types, constraints, or business rules 

3.  Determine how this entity fits into the overall MongoDB schema:

  - Should it be a separate collection? 

  - Should it be embedded in another document? 

  - Should it be a reference to another collection for lookups or relationships? 

  - Explore the benefit of denormalization for performance and business needs 

4.  Consider the data access patterns and how this entity will be used in the application.

# MongoDB Schema Design 

Using the analysis, suggest how the `<business_entity>` should be represented in MongoDB: 

- The name of the MongoDB collection that will represent this entity 

- List each field in the collection, its type, any constraints, and what it maps to in the original business context 

- For fields that are embedded, document the parent collection and how the fields are nested. Nested fields should follow the format `parentField->childField`. 

- For fields that are referenced, document the reference collection and how the lookup will be performed. 

- Provide any additional notes on indexing, performance considerations, or specific MongoDB features that should be used 

- Always use pascal case for collection names and camel case for field names 

# ERD Creation 

Create or update the Mermaid ERD in `mongo-erd.md` to include the results of your analysis. The ERD should reflect: 

- The new collection or embedded document structure 

- Any relationships with other collections/entities 

- The data types, constraints, and business rules that are relevant for MongoDB 

- Ensure the ERD is clear and follows best practices for MongoDB schema design 

Each entity in the ERD should have the following layout: 

1.  **Entity Name**: The name of the MongoDB collection / schema

2.  **Fields**: A list of fields in the collection, including:

    - Field Name (in camel case) 

    - Data Type (e.g., String, Number, Date, ObjectId) 

    - Constraints (e.g. indexed, unique, not null, nullable) 

In this example, Liquibase was used as a changelog to supply the necessary context, detailing entities, columns, data types, and relationships. Based on this, Copilot could offer architectural recommendations for new document or collection types, including whether to embed documents or use separate collections with cache references for lookup data. 

Copilot can also generate an entity relationship diagram (ERD), allowing for review and validation before proceeding. From there, a new data access layer can be generated, configurable to switch between SQL Server and MongoDB as needed. 

While production environments typically standardize on a single database model, this demonstration showcased the speed and flexibility with which strategic architectural components can be introduced using GitHub Copilot. 

👨‍💻Conclusion 

This modernization initiative demonstrated how strategic use of automation and best practices can transform legacy Microsoft Access solutions into scalable, maintainable architectures utilizing Node.js, SQL Server, MongoDB, and OpenAPI. By carefully planning each migration layer—from database and API specifications to business logic—the team preserved core functionality while introducing modern standards and enhanced capabilities. GitHub Copilot played a pivotal role, not only speeding up redevelopment but also improving code quality through automated documentation, test generation, and meaningful naming conventions. The result was a significant reduction in development time, with a robust, standards-compliant system delivered in just two weeks compared to an estimated six to eight months using traditional manual methods. 

This project serves as a blueprint for organizations seeking to modernize their Access-based applications, highlighting the efficiency gains and quality improvements that can be achieved by leveraging AI-powered tools and well-defined migration strategies. The approach ensures future scalability, easier maintenance, and alignment with contemporary enterprise requirements.