Microsoft Access to the Cloud: Approaches for Local Databases

Microsoft Access has been quietly running Australian businesses for thirty years. It is genuinely capable software: it can hold a relational database, run forms and reports, and automate workflows through VBA macros — all without a server, without IT, and without a software development budget. For small teams that needed a custom database in the 1990s or 2000s, it was often the only realistic option.

The problem is the same one that catches every locally-hosted database eventually: the world moved to the cloud, and Access did not. It lives in a file on a shared drive or a local machine. Remote workers can't get to it reliably. Mobile devices can't use it at all. Modern SaaS platforms have no way to connect to it. And as the file grows and multiple users write to it simultaneously, corruption and performance problems compound.

Most businesses with an Access database reach a point where the question isn't whether to move, but how.

There are five main approaches, ranging from a quick architectural change that keeps Access in place to a complete rebuild. The right choice depends on how complex the database is, how business-critical the workflows are, and how much disruption the team can absorb.

Why this comes up

Access databases tend to surface as a problem when one of several things happens:

• Remote work. The database lives on a network share. Staff working from home map the drive over VPN, but performance is unusable and simultaneous edits cause locking conflicts or corruption.
• The file is approaching its size limit. Access databases have a hard 2 GB limit per file. A database that has been running for years, collecting records and attachments, will eventually hit this. Splitting the database or archiving records buys time, not a solution.
• Multi-user corruption. Access was not designed for concurrent multi-user write access over a network. As user numbers grow, record locking failures and file corruption become regular events.
• The person who built it has left. Access databases are often built and maintained by one person who understood both the database and the VBA macros that hold the logic together. When that person leaves, nobody else fully understands what it does — and any change feels dangerous.
• Integration demands. The business now uses Xero, a CRM, or some other cloud platform, and staff are manually rekeying data between Access and those systems. Connecting Access to modern APIs requires custom VBA code and third-party drivers.
• The machine it runs on needs replacing. A hardware refresh or OS upgrade is coming, and nobody wants to invest in new equipment to keep running a system they're already uncertain about.

Your options at a glance

Five paths exist from local Access to cloud-accessible. They range from a quick backend swap to a full rebuild:

1. Access frontend + Azure SQL backend — Move the data tables to Azure SQL while keeping the Access forms and reports. Users continue working in Access, but the data lives in the cloud.
2. SharePoint Lists + Power Apps — Replace Access with SharePoint as the data store and Power Apps as the interface. Microsoft's recommended path for simpler workflows.
3. Power Platform + Dataverse — A more capable version of the above, using Dataverse as the backend for complex data models and Power Apps, Power Automate, and Power BI as the toolchain.
4. Progressive extraction — Migrate modules out of Access one at a time into a custom web application using the strangler fig pattern. Low risk, longer timeline.
5. Full custom replacement — Rebuild the entire application on a web-native stack. Highest cost and risk, but delivers the cleanest long-term result.

Option 1: Access frontend + Azure SQL backend

Access has always supported a split architecture: the forms, reports, and queries live in a "frontend" .accdb file on each user's machine, while the data tables live in a separate "backend" file. That backend file doesn't have to be another Access file — Access can link tables to SQL Server or Azure SQL via ODBC.

The migration steps are: export the Access tables to Azure SQL, set up ODBC connections on each user's machine, relink the tables in Access to point at Azure SQL instead of the local backend file. The forms, reports, queries, and VBA macros continue to work as before.

What it solves

• Eliminates the 2 GB file size limit — Azure SQL scales to terabytes
• Resolves corruption and locking issues — SQL Server handles concurrent writes properly
• Data now lives in the cloud with proper backups, high availability, and security
• Users continue working in Access exactly as before — zero retraining
• Fastest path to a more stable system: weeks rather than months

What it doesn't solve

• Access still needs to be installed on every user's machine
• Remote users still need a fast, stable internet connection — Access over ODBC to a remote SQL database is sensitive to latency
• No browser or mobile access
• The VBA maintenance burden remains; platform dependency on Access continues
• Microsoft Access licences still required for all users

When it makes sense

This approach makes sense when the primary pain is corruption, data loss risk, or the 2 GB limit — not the Access platform itself. If users are happy with how the application works and just need the data to be more reliable and scalable, this is the quickest win. It also works well as a first step toward a longer migration: stabilise the data, then tackle the frontend at a separate stage.

Option 2: SharePoint Lists + Power Apps

Microsoft's official recommended path for migrating Access to the cloud is SharePoint Lists as the data store and Power Apps as the interface. If your organisation already uses Microsoft 365, both are included in most plans at no extra cost.

SharePoint Lists store data in a structure similar to Access tables. Power Apps provides a form-building environment that can create screens resembling Access forms. Power Automate replaces basic VBA macros for workflow automation.

What it solves

• No Access licence required — runs entirely in the browser and on mobile
• Data lives in Microsoft's cloud with enterprise backup and security
• Works on any device: desktop browser, tablet, phone
• Integration with the rest of Microsoft 365 (Teams, Outlook, Excel) is straightforward
• May be included in your existing Microsoft 365 subscription

Real limitations to understand

• SharePoint Lists are not a relational database. They handle simple flat data well, but complex relationships, joins, and multi-table queries that Access handles easily are awkward or impossible in SharePoint Lists. The 5,000-item view threshold is a well-known performance limitation.
• Power Apps has a formula language, not VBA. Any business logic in VBA macros needs to be rebuilt from scratch in Power Apps' expression language. Complex logic may not be expressible at all.
• Access reports don't migrate. Power Apps doesn't generate printable reports — you'd use Power BI or export to Excel for reporting.
• Licensing adds up. Power Apps per-user plans cost money on top of Microsoft 365 for anything beyond basic usage.

When it makes sense

SharePoint + Power Apps works well for relatively simple Access databases: a few tables, straightforward data entry forms, basic workflows triggered by record creation or status changes. If the Access database has many complex queries, intricate relationships, or substantial VBA logic, this path will frustrate you.

Option 3: Power Platform + Dataverse

Dataverse (formerly Common Data Service) is Microsoft's cloud database built to underpin Power Apps at enterprise scale. Unlike SharePoint Lists, Dataverse is a proper relational database with enforced relationships, server-side business rules, role-based security, and no arbitrary row limits. Power Apps Canvas apps or Model-driven apps sit on top of it, Power Automate handles workflow automation, and Power BI provides reporting.

What it solves — beyond SharePoint Lists

• Proper relational data model with referential integrity
• Server-side calculated fields and business rules — logic doesn't live only in the app
• Row-level security that goes beyond SharePoint's permission model
• Model-driven apps automatically generate forms and views from the data model, reducing how much UI needs to be hand-built
• Scales to enterprise data volumes without the limitations of SharePoint Lists

What it doesn't solve

• Dataverse licensing is more expensive than SharePoint — it requires Power Apps per-app or per-user plans over and above Microsoft 365
• VBA business logic still needs to be rewritten in Power Fx or Power Automate flows
• Complex Access reports need rebuilding in Power BI
• The Power Platform development model is different from traditional development — internal staff who can maintain it need specific training

When it makes sense

Power Platform + Dataverse suits organisations already invested in the Microsoft ecosystem, with moderate-complexity Access databases, and an appetite to build and maintain in the Power Platform toolchain. It is a genuine step up from SharePoint Lists for anything with real relational data requirements. For very complex Access applications with substantial custom business logic, it may still not be sufficient.

Option 4: Progressive extraction

The strangler fig pattern applied to Access: rather than replacing the entire system at once, a new web application is built alongside it and modules migrate across one at a time. Access continues running throughout and is only retired when it has nothing left to do.

How it works in practice

1. Move the data first. Migrate Access tables to Azure SQL or PostgreSQL. This can often be done without touching the Access frontend — linked tables keep Access working while the data moves to a proper backend.
2. Map the application modules. Identify natural boundaries: customer records, job management, invoicing, stock control, reporting. Each module that can be independently migrated is a candidate for the first wave.
3. Build the new platform alongside Access. Choose a web technology stack. The new platform reads and writes to the same Azure SQL database that Access is now linked to — no sync layer needed if you've already done step one.
4. Migrate low-risk modules first. Reporting is often a good starting point — high value, low complexity, and easy to verify that outputs match.
5. Retire Access module by module. As each module stabilises in the new platform, disable it in Access. When nothing remains, Access is retired.

Advantages

• Lowest risk path to full modernisation
• Moving data to Azure SQL first (step one) delivers immediate stability benefits before a single line of new application code is written
• Delivers value to users incrementally rather than making them wait for a full rebuild
• Budget can be applied progressively — the project doesn't need to be funded all at once

Disadvantages

• Slower — a full migration of a complex Access database can take 12–24 months
• Requires ongoing project discipline to avoid leaving half the application in Access indefinitely
• Access and the new system need to coexist and share data cleanly during the transition period

Option 5: Full custom replacement

Rebuild the Access application from scratch as a web application. Start from the data model, rewrite the business logic, build the interface fresh. Access is retired when the new system goes live.

When it makes sense

• The Access database is genuinely unmaintainable — nobody understands the VBA, the schema has accumulated years of contradictions, and the original developer is long gone
• Business requirements have changed substantially and the existing logic is not worth preserving
• The business wants capabilities the Access model can never provide: mobile-native apps, real-time collaboration, public-facing web interfaces, deep integrations with cloud platforms
• A good off-the-shelf product now exists that covers 80%+ of what the custom Access database does

The risks

• Highest upfront cost and longest timeline before users see the new system
• VBA logic that nobody fully understands will surface as missing functionality after go-live
• Users who have worked in Access for years often resist a new interface, regardless of how much better it is
• Big-bang cutover carries the highest risk — if the new system isn't right on day one, there is no fallback

Choosing the right approach

The most practical sequence for complex databases: start with the Azure SQL backend swap to immediately resolve corruption and size issues, then build the new web application against that same SQL database using progressive extraction. You get stability quickly, and the full modernisation happens incrementally rather than as a single high-risk project.

Data and migration considerations

Data type mapping

Access data types don't map cleanly to SQL Server or Azure SQL. The migration requires deliberate decisions about each field:

• OLE Object fields store embedded files (images, Word documents, PDFs) directly inside the database. These need extracting to cloud object storage (Azure Blob Storage or S3) as part of any migration — they cannot simply be cast to a SQL column.
• Memo fields (long text in newer Access) map to SQL's NVARCHAR(MAX), but if any application code assumes a length limit, it needs reviewing.
• Date/Time fields in Access store both date and time but handle timezone differently to SQL Server. Verify date arithmetic in queries after migration.
• Multi-value fields are an Access-specific feature with no direct SQL equivalent. Each multi-value field effectively hides a child table — it needs to be normalised out into a proper junction table before the data can be moved.
• AutoNumber fields become IDENTITY columns in SQL, but if the Access database has gaps in the sequence (from deleted records), the migration needs to account for this to avoid referential integrity problems.

VBA macros and business logic

VBA is the hardest part of any Access migration. Access macros and VBA modules embed business logic — validation rules, calculations, workflow triggers, data transformations — that is not visible in the data model and cannot be automatically migrated to any other platform. Cataloguing this logic before migration starts is essential and usually takes longer than expected.

Common VBA patterns and their equivalents in a new system:

• Form validation → server-side model validation or database constraints
• BeforeUpdate/AfterUpdate event handlers → database triggers or application middleware
• Report generation → a reporting library, Power BI, or a dedicated reporting service
• Automation macros → scheduled jobs or event-driven workflows

Access queries

Saved Access queries often contain business logic in the form of calculated fields, complex joins, and parameter prompts. Most Access SQL translates to T-SQL with minor adjustments, but Access-specific functions (like Format(), IIf(), and Nz()) need equivalent replacements in the target database or application layer.

Data quality

Long-lived Access databases accumulate data quality problems. Fields used for multiple purposes depending on context. Required fields with blank values because Access didn't enforce the constraint consistently. Duplicate records that manual processes created over the years. Budget time for data cleansing before migration — moving dirty data to a new system just relocates the problem.

Access reports

Access reports are often underestimated in scope. They may include complex groupings, calculated totals, conditional formatting, and sub-reports. None of this migrates automatically to any replacement system. Inventory every report in the database and determine which ones are actively used before committing to rebuilding them — many reports in old Access databases haven't been run in years.

FAQ

Can we just put the Access file in SharePoint or OneDrive for cloud access?

No, and Microsoft explicitly advises against it. Access databases stored in SharePoint document libraries or OneDrive can open in desktop Access, but simultaneous edits cause corruption because Access requires exclusive file-level locking that cloud file sync does not support. This will corrupt your database. The only safe shared-file approach is storing the backend on a traditional Windows file server, not a cloud sync folder.

How much does migrating an Access database typically cost?

Moving the backend to Azure SQL is primarily an infrastructure and configuration cost — often $3,000–$8,000 depending on the complexity of the table structure and how many linked tables need reconfiguring. A Power Apps replacement of a simple Access database might run $10,000–$30,000. A full custom web replacement of a complex multi-form, multi-report Access application can run $50,000–$200,000+. Progressive extraction spreads these costs over time and typically delivers a lower total cost than a single full rebuild.

Does Microsoft 365 Business or Enterprise include Power Apps?

Microsoft 365 plans include a limited version of Power Apps. Apps that only read and write data from Microsoft 365 sources (SharePoint, Teams, Excel Online) are included. Apps that connect to Dataverse or premium connectors (Xero, Salesforce, SQL Server) require Power Apps per-app or per-user plans, which are additional costs on top of Microsoft 365 licensing. Check exactly which connectors your replacement app will need before assuming it's covered.

Our Access database is corrupting regularly — what should we do first?

In the short term: compact and repair the database, ensure all users are running the same version of Access, and split the database into frontend and backend if it isn't already split. Move the backend file to a dedicated file server rather than a NAS or consumer network storage device. These steps buy time. For a medium-term fix, moving the backend to Azure SQL (Option 1 above) resolves the corruption problem properly without requiring any application rebuilding work.

What if the person who built the database has left and nobody understands the VBA?

Start with a system audit. Open the VBA editor and work through every module and macro — document what each one does by reading the code and observing its effects in the running application. Interview every person who uses the database and document the workflows they depend on. This discovery process takes longer than businesses expect, but it is the only way to understand what you're migrating. Skipping it guarantees that critical functionality will be missed, and users will discover it after go-live.

Should we buy off-the-shelf software instead of rebuilding?

Possibly. Access databases often exist because no off-the-shelf product covered the business's specific needs at the time it was built. That may have changed. Before committing to a custom rebuild, evaluate whether modern SaaS platforms now cover your core use case. Industry-specific software (job management, inventory, CRM, field service) has matured significantly. If a product covers 80% of what your Access database does and you can adapt processes for the rest, buying is almost always faster and cheaper than rebuilding.

Kasun Wijayamanna Founder & Lead Developer

Postgraduate Researcher (AI & RAG), Curtin University - Western Australia

View profile →