Replacing an Access database or Excel solution: SME guide

Warning signs: when Access or Excel reaches its limits

Problems rarely arrive overnight; they creep in. These signs suggest the solution has outgrown its original purpose:

• Size: according to Microsoft, an Access database file (.accdb) can be at most 2 GB, minus the space needed for system objects; the same limit applies to a single table. An Excel worksheet holds at most 1,048,576 rows and 16,384 columns.
• Multiple users: Microsoft allows up to 255 concurrent Access users. If the whole file sits on a network share, Microsoft says all objects travel across the network, slowing things down, and every user has a full copy, which is less secure. The usual remedy is splitting it into front end and back end.
• Excel with several people: Microsoft says the old “shared workbook” feature has many limitations and recommends co-authoring. That needs a current version such as Excel for Microsoft 365 and storage in OneDrive or SharePoint Online; older versions cannot edit simultaneously.
• Remote access: Microsoft describes the split Access database for a local area network (LAN) and advises against opening it from OneDrive or a SharePoint document library, because duplicate copies can appear. Access itself is not available for macOS, according to Microsoft.
• Permissions: Microsoft notes that database servers usually offer account-based security defining who sees which tables. With a file on a network share, you control access through share and file permissions instead.
• Versions: copies such as “customers_final_new.xlsx” circulate, nobody knows which one is current, and changes cannot be traced.
• Knowledge held by one person: only whoever built the solution understands the macros, VBA code and queries. If that person is unavailable, the process stops.

If several apply, look at alternatives before an outage forces the decision.

What Microsoft sets: support end dates and retired web features

After end of support, Microsoft says there are no more security updates, no other updates and no support. The programs may keep running, but Microsoft warns that they can expose you to security risks. For Access the situation is as follows:

Three routes to a replacement

There are three broad routes, which can be combined. Which fits depends on how much of the interface should stay, who needs access from where, and how much logic the old solution holds.

Access as the front end, SQL Server as the back end

The data moves to SQL Server; Access stays as the interface via linked tables. Microsoft itself cites the 2 GB and 255-user limits as the reason and provides the SQL Server Migration Assistant (SSMA). It mainly migrates tables and select queries without parameters; forms, reports, macros, VBA modules and tables with attachment columns are not migrated. The free SQL Server Express is a good start: per Microsoft, version 2025 handles up to 50 GB per database (the Access guide still says 10 GB), but uses at most four cores and has no SQL Server Agent for scheduled jobs. This route keeps familiar forms but stays tied to Windows PCs with Access.

Low-code with Power Apps and Dataverse

A built-in Access tool exports tables and data to Dataverse, the Microsoft Power Platform’s data platform. Per Microsoft, users can keep working in Access and also build apps for desktop, phone and tablet. Dataverse does not support every Access data type, though. Check licensing closely: Dataverse comes with Power Apps or Dynamics 365 licences, while the Power Apps rights included in some Microsoft 365 licences cover only Microsoft 365 data and standard connectors. In Power Apps, the SQL Server connector is premium. A base licence includes 10 GB of relational database; more capacity can be bought.

Your own web application

A web application runs in the browser, on your own server or with a host, without Access and without a tie to Windows. Roles, permissions, logging and interfaces are built to fit the process. In return, the interface is built from scratch, and development, operation and maintenance cost money. Whether that pays off is covered in the guides Custom software or off-the-shelf software and What does custom software cost?.

The three routes compared

The table compares criteria, not scores. Which column fits depends on your process, your devices and the licences you already have.

Replacing the solution in six steps

• 1. Inventory: which tables, queries, forms, reports, macros and VBA modules exist, who uses them for what, which Excel exports depend on them? The result is a list of what the new solution must do and what can be dropped.
• 2. Data model: grown tables become a clean model. Before migrating to SQL Server, Microsoft’s first step is that every table has an index and a primary key and relationships use fields of consistent type and size.
• 3. Data cleansing: duplicates, empty mandatory fields and free text instead of fixed values are resolved before the migration. Multivalued fields and attachment fields need their own rules, because according to Microsoft SSMA does not carry them over as a proper structure. Tax-relevant data sets a limit here, see retention.
• 4. Migration: a trial run with a copy, then record counts and spot checks, then the real move. Beforehand, Microsoft advises closing the database, making sure all users have closed it and taking a backup.
• 5. Parallel operation: for a limited time old and new run side by side, the old solution ideally read-only. Gaps show up without bringing work to a halt. A fixed cut-off date ends this phase; otherwise everyone maintains data twice.
• 6. Training and handover: a short introduction per role, a guide to common tasks and documentation, so the knowledge does not end up with one person again.

Typical risks and how to handle them

• Hidden logic: macros, VBA modules, form events and queries can hold business rules such as price calculations or plausibility checks. SSMA does not convert them and a Dataverse migration only moves tables and data. The rules therefore survive only in the Access front end; for any new interface, every rule must be found and rebuilt. If Access stays as the front end, check the code for VBScript dependencies.
• Data quality: a database with fixed rules rejects values that Access or Excel tolerated. Data types do not always map one to one either: Microsoft recommends a deliberate mapping for date and time values in SQL Server, and Dataverse has different limits from Access for some types.
• Dependencies on Excel exports: someone often pulls data into Excel and sends reports to accounting or customers. These paths break when the source changes. List all exports and recipients and decide whether the new solution replaces or keeps supplying them.

Data protection and retention: a brief orientation

If the solution holds personal data, the GDPR applies whatever the technology. Data may only identify people for as long as its purpose requires (Art. 5(1)(e)). Security measures appropriate to the risk include the ability to ensure the ongoing confidentiality, integrity and availability of systems and to restore data promptly after an incident (Art. 32(1)(b) and (c)). A service provider processing the data on your behalf needs a data processing agreement (Art. 28). The migration is a good moment to delete data you no longer need, unless a retention obligation applies.

Tax rules apply if the solution, say, issues invoices, records working hours or tracks stock: the GoBD, the German Finance Ministry’s administrative directive, also count secondary systems such as invoicing, time recording and inventory management as data processing systems (para. 20). After a system change, the old hardware and software need not be kept for the retention period only if all records subject to retention, including metadata, master and transaction data and their links, move to the new system in equal quantity and quality and allow the same evaluations there. Migration may convert the format, not the content, and changes must be documented (paras. 142 and 143). So do not alter tax-relevant historical records during cleansing. Under Section 147 of the German Fiscal Code (AO), retention periods are ten, eight or six years depending on the document. More on the GoBD in the guide Exchange Online and the GoBD.

The next step

The starting point is not a technology decision but the inventory. It tells you whether a SQL Server back end is enough, Power Apps fits or your own application pays off. I develop web applications and interfaces, using .NET, TypeScript, SQL Server and PostgreSQL among others; how I work is described on the software development page. Servers, backup and networking for a new database back end are part of my IT infrastructure services.