In short
Start by checking your version and limits: support for Access 2016, 2019 and 2021 has ended. If the interface still does the job, move the data to a database server and keep Access. Otherwise, build a web app, because Microsoft’s migration tool doesn’t convert forms, reports or VBA. Afterward, verify rows, totals and attachments.
Key takeaways
- Support for Access 2016 and 2019 ended on October 14, 2025, and for Access 2021 on October 13, 2026. Access 2024 runs until October 9, 2029.
- SQL Server Migration Assistant (SSMA) mainly moves tables and select queries without parameters, but no forms, reports, macros or VBA.
- The interim step with a database server makes room for more data and users. Windows, Access and the VBA code stay.
- When you reconcile, count Yes values instead of adding them up. In our test, the total of two Yes values was -2.
- Through OLE DB, attachments and multivalued fields show up as text. Only the Access library DAO names the field type.
Do you need to replace your Access database now?
Only if your Access database runs into limits an upgrade doesn’t fix: people working without a Windows PC, data volume, user count, or VBA only one person understands. Access isn’t going away: Access 2024 and Access in Microsoft 365 are supported. Support for Access 2016 and 2019 ended on October 14, 2025, and for Access 2021 on October 13, 2026.
Why: After the end of support, Microsoft says, there are no new security updates. Moving to a supported version closes that gap. The other limits remain: an Access file holds 2 GB minus the space needed for system objects, and at most 255 people can work in it at the same time (Access specifications (external site)). Access runs on the Windows desktop and comes only with certain Microsoft 365 plans (Microsoft on Access and Excel (external site)). On remote access over a VPN, Microsoft writes: “Access and Virtual Private Networks (VPN) don’t get along.” (Microsoft on migrating to SQL Server (external site))
| Version | End of support | What it means for you |
|---|---|---|
| Access 2016 and 2019 | Oct 14, 2025 | No new security updates. |
| Access 2021 and Access LTSC 2021 | Oct 13, 2026 | No new security updates. |
| Access 2024 | Oct 9, 2029 | Still supported. |
| Access in Microsoft 365 | no fixed end date | Supported as long as you’re licensed and install updates. |
Sources for the table: end of support in 2025 (external site), end of support in 2026 (external site), end of support in 2029 (external site), Microsoft 365 Apps lifecycle (external site), Modern Lifecycle Policy (external site).
How to do it:
- Note the Access version on each workstation. If a version is out of support, plan the move to a supported version, even if you’ll replace the database later.
- Record the file size, the number of people working at the same time, locations and any access from outside your network.
- Ask who can still change the VBA code and macros.
- Estimate how many hours a week disruptions and workarounds cost. Our how-to Digitizing business processes shows how to measure that.
Example: Say a maintenance company with 20 employees schedules its jobs in an Access database on the office server. Everyone uses Access 2016, the file is 1.4 GB, and a former colleague wrote the VBA. In the future, the field technicians are supposed to confirm jobs on the go.
Common mistake: The end of support alone triggers a rebuild. If the interface still does the job, moving to a supported version is often enough.
Done when the version, file size, number of users, ways of access and the person who knows the code are in writing.
Which path fits: an interim step or a full web app?
First check whether an existing product covers the process. If not, how people work decides: anyone working without a Windows PC or on the go needs a web app. If only data volume, user count or the network are the problem and the interface fits, the interim step is enough: the data moves to a database server and Access stays.
| Option | Fits if | Limit |
|---|---|---|
| Update and split Access: a supported version, with data and interface in separate files | a few people work on the local network and the interface fits | Access needs Windows; data and logic stay in Access files. |
| Interim step: data on a database server, Access stays as the interface | data volume, user count or availability are the problem and the forms are still good enough | Access or Access Runtime on every workstation; the VBA stays and needs adjustments. |
| Off-the-shelf software or a low-code platform | a product meets all must-have requirements through its settings | Your process adapts to the product, and licenses are an ongoing cost. |
| Web app built for your process | people work without a Windows PC, on the go or from outside | Forms, reports and VBA get rebuilt; higher upfront cost and ongoing maintenance. |
How to do it: Answer the questions in order. The first one you can answer with yes names your path.
- Does an existing product meet all your must-have requirements? Then use the product. Our comparison Build or buy software? shows when buying pays off and when building does. Whether a platform will do is covered in Low-code vs. custom development.
- Do people have to work without a Windows PC, on the go or from outside your network? Then the path leads to a web app, because Access is a Windows desktop program. Remote access to a Windows PC can bridge the gap for individuals, but it doesn’t replace an app in the browser.
- Do you want to get rid of the VBA, or does only one person still understand it? Then the interim step won’t help much, because the VBA stays where it is. Plan the web app or have the code documented first.
- Is the only problem data volume, user count or the network? Then choose the interim step.
- None of these apply? Then stay with Access, move to a supported version and split the database (Split an Access database (external site)).
The interim step can be the first step toward a web app: once the data is on a server, the new app can work alongside Access on the same data. After the move, Microsoft names both ways forward: continue with linked tables, or manually move the application to code that works directly with the server (Microsoft on preparing for migration (external site)).
During a process check, we work through your process with you and start by checking whether off-the-shelf software would do the job. What’s in a process check is described on our custom business software page.
Common mistake: The team picks the interim step even though access from outside is the real problem. The interface then stays tied to Windows.
Done when all five questions are answered and the chosen path is in writing, with the reason.
How does the interim step with linked tables work?
The tables live on a database server, and an Access file on each workstation connects to them through linked tables. Forms, reports, queries and VBA stay in Access. That gives you room for more data and more users, but every workstation still needs Windows and either Access or the free Access Runtime.
Why: Microsoft compares migrating to a split database, where the tables sit in the back end and the forms, reports and queries in the front end. In Microsoft’s description, SQL Server acts as the new back end. According to Microsoft, it handles many more concurrent users and can be backed up while it’s in use. Access also links to other data sources through ODBC (Manage linked tables (external site)). Which database server fits is a decision for the project. The free Access Runtime (external site) runs the database without Access installed, but it can’t change the design.
| Where | What happens | What to do |
|---|---|---|
| Queries with Access functions or VBA | Access pulls the data to the local computer and calculates there itself, often slowly. | Convert them to pass-through queries, views or stored procedures on the server. |
| Queries across local and linked tables | Access downloads all the server data to the local computer. | Avoid them; keep only lists that rarely change local. |
| AutoNumber in VBA code | The new value is available only after the record is saved. | Adjust the code. |
| Changes to the table structure | No longer possible in Access. | Make them on the server and update the link. |
| Sign-in with a SQL Server account | The password is stored in clear text in the linked tables. | Use Windows authentication. |
| Date values with fractional seconds | Linked tables show #Deleted or write conflicts. | Add a rowversion column on the server. |
Sources: linked tables after the move (external site), Microsoft on #Deleted (external site), Microsoft on migrating to SQL Server (external site).
How to do it:
- Back up the database before you split or move it. Microsoft advises this too.
- Put the file with the forms and reports on every workstation, because Microsoft says each person works with a local copy.
- Before the migration, create the assessment report in Microsoft’s migration tool, SQL Server Migration Assistant (SSMA). It shows errors, warnings and the steps to take before you move anything.
- After the move, test every form and report in a test environment, using cases from the last few weeks.
Common mistake: The file with the forms stays on the network drive, and everyone opens the same copy. Microsoft calls for one local copy per person.
Done when every form and report works in the test environment and no password is stored in clear text in the links.
What belongs in an Access inventory?
Besides tables and columns, list the relationships, queries, linked tables, forms, reports, macros, VBA modules, attachments and multivalued fields. Note what’s really used day to day and who knows it. A script can read the technical inventory; the business rules in forms and VBA only show up when you open them in Access.
Why: Anything that’s not on the list will be missing later. How to record files and columns in general is explained in the section taking stock of your files of our Excel how-to. Access adds these objects:
| Object | What you need to know | Where to find it |
|---|---|---|
| Tables, columns, primary keys | Data type, field size, missing key | Script, Database Documenter |
| Relationships | Linked fields, behavior on delete | Script, Relationships window |
| Queries | Select or action, parameters, calls to VBA | Script (VIEW, PROCEDURE, DAO type) |
| Linked tables | Where they point and whether the target still exists | Script, Linked Table Manager |
| Attachments, multivalued fields, hyperlinks, calculated fields | Each special type needs its own migration path | Script via DAO |
| Forms, reports | Validation rules, printouts, what’s still needed | Count via DAO, content only in Access |
| Macros, VBA modules | What the code does and who knows it | Count via DAO, code only in Access |
How to do it:
- Work on a copy. For old .mdb files, remove user-level security first, because Microsoft says SSMA can’t migrate databases that use workgroup protection.
- Run the script under From our work on the copy.
- Have Access document the design characteristics: Database Tools tab, Analyze group, Database Documenter (external site). For the VBA code, open each module.
- Ask the people who work with it every day which forms and reports they really need. What nobody uses doesn’t have to be rebuilt.
- Check whether other programs access the file, such as Excel reports or mail merges.
Example: In the example company, the inventory finds 38 tables, 112 queries, 64 forms and 21 reports. According to the team, 23 forms and nine reports are still needed.
Common mistake: The script is treated as the complete inventory. It only counts forms, reports and VBA; it doesn’t know what’s in them.
Done when every object has a plan (migrate, rebuild or drop) and for every rule it’s clear who knows it.
Which data types and relationships need their own decision?
Microsoft’s SSMA tool maps most Access data types automatically. Attachments, multivalued fields, hyperlinks, Yes/No fields, decimal numbers and dates before 1753 need a decision of their own. So do tables without a primary key and relationships with unequal field sizes, because both cause errors during the move or tables you can no longer change.
Why: Microsoft documents the default mapping for SQL Server, which is why it’s shown here. With another database server the types have different names, but the questions are the same.
| Access type | Default target in SQL Server | What to watch for |
|---|---|---|
| Short Text, Long Text | nvarchar(n), nvarchar(max) | Usually not a problem. |
| Number: Decimal | float | float is an approximate type. For amounts, map to decimal by hand. |
| Number: Byte, Integer, Long Integer; Currency | tinyint, smallint, int; money | Usually not a problem. |
| Date/Time | datetime | SQL Server accepts values from Jan 1, 1753, and Access from the year 100. |
| Yes/No | bit | Access stores Yes as -1, the bit type as 1. Create the field without NULL to avoid record locking. |
| OLE Object | varbinary(max) | Check whether the content is still readable afterward. |
| Hyperlink | nvarchar(max) | The column becomes text and loses its link function there. In Access forms, you can turn it back on with a property. |
| Attachment | none: SSMA doesn’t migrate such tables | Extract the files first and store them elsewhere. |
| Multivalued field | unclear: Microsoft says both | Plan a separate junction table. |
| Calculated field | not listed | Check the expression and the result individually. |
Sources: default mapping in SSMA (external site), comparing data types (external site), data types in Access (external site), float and real (external site), bit in SQL Server (external site), migrating to SQL Server (external site), incompatible features (external site).
Multivalued fields: The same Microsoft page says the field “is converted to SQL Server as an ntext field” and, a few lines later, “Multivalued fields are not converted.” We didn’t test the migration with SSMA, only reading the field (From our work). Plan a separate junction table, also because Microsoft says ntext (external site) will be removed in a future version.
Early dates: Access accepts typos. In our test, the database stored the entry #0202-02-01# in the year 202. SSMA can replace such values with NULL or with the earliest supported date (settings for data migration (external site)). So check these rows individually beforehand.
| Finding in Access | Consequence | What to do |
|---|---|---|
| Table without a unique index | Can’t be changed after the migration. | Add a primary key or have SSMA create one. |
| Relationship with unequal types or field sizes | The foreign key constraint isn’t created. | Align the type and size. |
| Relationship to a table without a primary key or unique index | SQL Server doesn’t support this. | Add a key. |
| Unique index with multiple empty values | Before SSMA 8.13, migrating these tables fails. | Adjust the values or the index. |
| Names with SQL keywords or special characters | Queries have to put the names in brackets; otherwise, errors can occur. | Rename them before the migration. |
| Default values and validation rules with Access functions | Can’t be loaded onto the server. | Rewrite the rule. |
How to do it:
- Assign a target type to every field in your inventory, and check the default mapping instead of accepting it unseen.
- For attachments, decide where the files will live. One option Microsoft names is extracting the files and storing only links to them on the server.
- Fix the findings from the table in a copy and note every change.
Example: In the example company, amounts are stored as Decimal numbers; they get the target type decimal by hand.
Common mistake: The default mapping is accepted without reading it. Amounts then end up as float in the new database.
Done when every field on the list has a target type, every table has a primary key and every special type has a described path.
What happens to queries, forms, reports and VBA?
Microsoft’s SSMA tool mainly migrates tables and select queries without parameters. It doesn’t convert forms, reports, macros or VBA modules. In the interim step, they keep running in Access, sometimes with adjustments; for a web app, they get built from scratch. So first write down the rules they contain.
Why: Forms and VBA often hold business rules: number ranges, checks, rounding. They only show up when someone reads them. In our assessment, that’s where the larger part of the work on a web app lies.
| Object | SSMA | Interim step | Web app |
|---|---|---|---|
| Select query without parameters | converted, sometimes with rework | runs through linked tables | becomes a query or view in the new app |
| Query with parameters, action query | not converted | stays in Access | becomes part of the app logic |
| Query with a VBA function | doesn’t run on the server | Access calculates locally; better as a pass-through query | rule rebuilt in the app or the database |
| Form | not converted | stays in Access | rebuilt, with validation rules and permissions |
| Report | not converted | stays in Access | rebuilt, for example as a view or PDF |
| Macro, VBA module | not converted | stays, sometimes with adjustments | rules reimplemented |
How to do it:
- Read through the forms and VBA for what they decide, and write each rule down as a sentence.
- Give each rule a place: in the database, in the app or nowhere anymore.
- Migrate parameter and action queries by hand. Microsoft lists them as not convertible.
- Rebuild forms around what users need to do instead of copying every field.
- Agree with the departments on which reports are needed as views, exports or PDFs.
Example: In the example company, VBA assigns order numbers by year and short code. This rule is written down as a sentence and reimplemented in the new app.
Common mistake: The new app is supposed to look like the old forms. That also copies the detours the team has long kept taking out of habit.
Done when every rule from forms, reports and VBA is written down and has a place or the note “dropped.”
How do you reconcile the data after the migration?
Before and after every trial run, compare the row count and the totals of amounts for each table. For Access, also count Yes values, dates before 1753 and entries in attachment and multivalued fields, and compare files one by one. Trial runs, parallel running and training work the way our Excel how-to describes.
Why: During the move, types and counting methods change. Without reconciliation, you only notice in day-to-day operation. Our Excel how-to covers the basic steps for trial runs and migration, parallel running and training your team. Here’s what Access adds.
| What you compare | How | What to watch for |
|---|---|---|
| Rows per table | COUNT(*) before and after | Count linked tables individually, even if the target is elsewhere. |
| Totals of amounts | SUM per amount field | Differences of a few cents suggest float instead of decimal. |
| Yes values | count, don’t add up | In Access, SUM returns negative values, and bit doesn’t directly support SUM. |
| Dates before 1753 | query on the date | Note what should happen to these rows before SSMA runs. |
| Attachments | number and file comparison, for example with SHA-256 | A column of binary data from OLE DB isn’t the actual file yet. |
| Multivalued fields | number of values per field | OLE DB returns the field as text with semicolons; only .Value shows each value. |
How to do it:
- Before each trial run, pull the reconciliation values from the copy with the script under From our work.
- Test with data that has no names or contact details if third parties get access, and check with your data protection advisor which contracts you need.
- After the trial run, get the same values from the new system and compare them line by line.
- Before the switch, clarify with your tax advisor how long and in what form to keep the old database, and plan how it can still be opened later.
Common mistake: The reconciliation only counts rows. An amount stored as float or a date in the year 202 goes unnoticed.
Done when all reconciliation values match before and after the trial run, or every difference is explained.
From our work: inventory and reconciliation on a test database
We ran the inventory and reconciliation on November 9, 2026, on a test database of our own with no real data. It contained three tables, one relationship, four queries, two linked tables (one with a missing target), attachment, multivalued, hyperlink and calculated fields, and a form, a report, a macro and a VBA module. The script below read it in Windows PowerShell 5.1 through the OLE DB interface with the provider Microsoft.ACE.OLEDB.16.0 and through the Access library DAO. The file stayed unchanged: its SHA-256 hash was the same before and after the run. Our test machine runs German Windows and Office, so the output uses decimal commas and error messages from the Access database engine appear in German.
Measured · Microsoft.ACE.OLEDB.16.0 (ACEOLEDB.DLL 16.0.20527.20016), Windows PowerShell 5.1, test database with no real data, inventory script
# Print the inventory and reconciliation values of an Access database (read-only, on a copy).
# Usage: powershell -File inventory-access.ps1 -Database C:\Test\Copy.accdb
param([Parameter(Mandatory = $true)][string]$Database)
$g = [System.Data.OleDb.OleDbSchemaGuid]
$db = New-Object System.Data.OleDb.OleDbConnection (
"Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$Database;Mode=Read")
$db.Open()
function Schema($type) { $db.GetOleDbSchemaTable($type, $null).Rows }
function Scalar($sql) {
try { $c = $db.CreateCommand(); $c.CommandText = $sql; $c.ExecuteScalar() }
catch { 'ERROR: ' + $_.Exception.InnerException.Message }
}
$own = { $_.TABLE_NAME -notmatch '^(MSys|~)' }
'== Tables and queries (LINK = linked, VIEW/PROCEDURE = saved queries)'
$objects = Schema $g::Tables | Where-Object $own |
Where-Object { $_.TABLE_TYPE -in 'TABLE', 'LINK', 'PASS-THROUGH', 'VIEW' }
$objects | Sort-Object TABLE_TYPE, TABLE_NAME |
ForEach-Object { '{0}: {1}' -f $_.TABLE_TYPE, $_.TABLE_NAME }
Schema $g::Procedures | Where-Object PROCEDURE_NAME -notmatch '^~' |
ForEach-Object { 'PROCEDURE: {0}' -f $_.PROCEDURE_NAME }
'== Tables without a primary key'
$withPk = (Schema $g::Indexes | Where-Object { $_.PRIMARY_KEY }).TABLE_NAME
$objects | Where-Object { $_.TABLE_TYPE -eq 'TABLE' -and $_.TABLE_NAME -notin $withPk } |
ForEach-Object TABLE_NAME
'== Relationships'
Schema $g::Foreign_Keys | Where-Object { $_.FK_TABLE_NAME -notmatch '^MSys' } | ForEach-Object {
'{0}.{1} -> {2}.{3} (on delete: {4})' -f $_.FK_TABLE_NAME, $_.FK_COLUMN_NAME,
$_.PK_TABLE_NAME, $_.PK_COLUMN_NAME, $_.DELETE_RULE }
'== Columns (type per OLE DB)'
$columns = Schema $g::Columns
$types = @{ 2='Integer'; 3='Long'; 4='Single'; 5='Double'; 6='Currency'; 7='Date'; 11='YesNo'; 17='Byte'; 72='GUID'; 128='Binary'; 130='Text'; 131='Decimal' }
foreach ($t in ($objects | Where-Object TABLE_TYPE -ne 'VIEW').TABLE_NAME) {
$list = $columns | Where-Object TABLE_NAME -eq $t | Sort-Object ORDINAL_POSITION | ForEach-Object {
$n = $types[[int]$_.DATA_TYPE]; if (-not $n) { $n = 'Type ' + $_.DATA_TYPE }
if ([int]$_.DATA_TYPE -eq 130 -and $_.CHARACTER_MAXIMUM_LENGTH -gt 0) { $n += "($($_.CHARACTER_MAXIMUM_LENGTH))" }
'{0}:{1}' -f $_.COLUMN_NAME, $n }
'Columns {0}: {1}' -f $t, ($list -join ', ')
}
'== Reconciliation values'
foreach ($t in ($objects | Where-Object TABLE_TYPE -ne 'VIEW').TABLE_NAME) {
'Rows {0}: {1}' -f $t, (Scalar "SELECT COUNT(*) FROM [$t]")
foreach ($s in ($columns | Where-Object TABLE_NAME -eq $t)) {
$col = "[$($s.COLUMN_NAME)]"
switch ([int]$s.DATA_TYPE) {
{ $_ -in 6, 131 } { 'Total {0}.{1}: {2}' -f $t, $col, (Scalar "SELECT SUM($col) FROM [$t]") }
11 { 'Yes values {0}.{1}: {2}' -f $t, $col, (Scalar "SELECT COUNT(*) FROM [$t] WHERE $col") }
7 { 'Dates before 1753 {0}.{1}: {2}' -f $t, $col,
(Scalar "SELECT COUNT(*) FROM [$t] WHERE $col < #1753-01-01#") }
}
}
}
'== Not visible in the OLE DB schema (via DAO)'
$dao = (New-Object -ComObject DAO.DBEngine.120).OpenDatabase($Database, $false, $true)
foreach ($t in $dao.TableDefs) {
if ($t.Name -match '^(MSys|~)') { continue }
if ($t.Connect) { 'Target {0}: {1}' -f $t.Name, ($t.Connect -replace 'PWD=[^;]*', 'PWD=***') }
foreach ($f in $t.Fields) {
$name = "[$($f.Name)]"; $kind = ''; $count = ''
if ($f.Type -eq 101) { $kind = 'Attachment'; $count = "$name.[FileName]" }
elseif ($f.Type -ge 102 -and $f.Type -le 109) { $kind = 'Multivalued'; $count = "$name.[Value]" }
elseif ([int]$f.Attributes -band 32768) { $kind = 'Hyperlink' }
elseif ($f.Expression) { $kind = 'Calculated' }
if ($kind) { '{0}.{1}: {2}' -f $t.Name, $f.Name, $kind }
if ($count) { 'Entries {0}.{1}: {2}' -f $t.Name, $name, (Scalar "SELECT COUNT($count) FROM [$($t.Name)]") }
}
}
foreach ($q in $dao.QueryDefs) { if ($q.Name -notlike '~*') { 'Query {0}: DAO type {1}' -f $q.Name, $q.Type } }
foreach ($k in $dao.Containers) {
if ($k.Name -in 'Forms', 'Reports', 'Scripts', 'Modules') { '{0}: {1}' -f $k.Name, $k.Documents.Count }
}
$dao.Close()
$db.Close()== Tables and queries (LINK = linked, VIEW/PROCEDURE = saved queries)
LINK: Stock_broken
LINK: Stock_ext
TABLE: Customers
TABLE: Orders
TABLE: PriceList
VIEW: OrderTotals
PROCEDURE: qryFromDate
PROCEDURE: qryGross
PROCEDURE: qryRaisePrices
== Tables without a primary key
PriceList
== Relationships
Orders.CustomerId -> Customers.CustomerId (on delete: CASCADE)
== Columns (type per OLE DB)
Columns Customers: CustomerId:Long, Name:Text(100), Notes:Text, Since:Date, Revenue:Currency, Active:YesNo, Attachments:Text, Tags:Text, Website:Text
Columns Orders: OrderId:Long, CustomerId:Long, Amount:Currency, OrderDate:Date, Gross:Currency
Columns PriceList: Item:Text(50), Price:Currency
Columns Stock_broken:
Columns Stock_ext: ItemNo:Long, OnHand:Long
== Reconciliation values
Rows Customers: 3
Yes values Customers.[Active]: 2
Total Customers.[Revenue]: 2000,5
Dates before 1753 Customers.[Since]: 1
Rows Orders: 3
Total Orders.[Amount]: 525,75
Total Orders.[Gross]: 625,6425
Dates before 1753 Orders.[OrderDate]: 0
Rows PriceList: 1
Total PriceList.[Price]: 9,99
Rows Stock_broken: ERROR: Datei 'T:\external-gone.accdb' nicht gefunden.
Rows Stock_ext: 1
== Not visible in the OLE DB schema (via DAO)
Customers.Attachments: Attachment
Entries Customers.[Attachments]: 1
Customers.Tags: Multivalued
Entries Customers.[Tags]: 2
Customers.Website: Hyperlink
Orders.Gross: Calculated
Target Stock_broken: ;DATABASE=T:\external-gone.accdb
Target Stock_ext: ;DATABASE=T:\external.accdb
Query OrderTotals: DAO type 0
Query qryFromDate: DAO type 0
Query qryGross: DAO type 0
Query qryRaisePrices: DAO type 48
Forms: 1
Modules: 1
Reports: 1
Scripts: 1| Test | Result | What it means for you |
|---|---|---|
| Reading the system table MSysObjects via OLE DB and DAO | “Keine Leseberechtigung auf 'MSysObjects’.” (German for: no read permission). Inside Access itself, it could be read. | From outside Access, go through the schema. |
| Column type of attachment and multivalued fields | OLE DB reports Text, DAO reports Attachment and ComplexText. | A script using OLE DB alone misses these fields. |
| Query that calls a VBA function | In Access it returns results; via OLE DB: “Undefinierte Funktion 'GrossAmount’ in Ausdruck.” (German for: undefined function). | Such queries only run in Access. |
| SUM over a Yes/No field with two Yes values | -2 | Count Yes values when you reconcile. |
| Reading an attachment via OLE DB | 44 bytes of binary data instead of the 24 bytes of the original file | Save attachments via DAO (SaveToFile): 24 bytes, same SHA-256 hash as the original. |
| Reading a multivalued field via OLE DB | “Framework;Regular” | With .Value you get one row per value, which helps with the junction table. |
| Writing in read mode (Mode=Read) | Refused: “Operation muss eine aktualisierbare Abfrage verwenden.” (German for: must use an updatable query). The control test with write access worked. | The script opens the file read-only. |
How to use the script: Close Access and save the script as a file. Run it on a copy with powershell -File inventory-access.ps1 -Database C:\Test\Copy.accdb. It needs a PowerShell that matches the bitness of Access: on our machine with 64-bit Office it ran under 64-bit PowerShell, while under 32-bit PowerShell it reported that the provider wasn’t registered. An older .mdb file could also be read this way. Compare the output before and after a migration with the values in the new system.
Limits: A small test file doesn’t prove that every database behaves the same way. The schema doesn’t include forms, reports or VBA; the script only counts them. We didn’t use SSMA or a SQL Server. What’s written here about both comes from Microsoft.
When we migrate data, we first agree with you on which data to migrate and how to clean up duplicate or incomplete records.
Frequently asked questions
Can you put an Access database on the internet?
Not with Microsoft’s own tools anymore. Microsoft shut down Access web apps and web databases in Microsoft 365 and SharePoint Online by April 2018 and names Power Apps as an alternative (Microsoft on Access web apps (external site)). According to Microsoft, the Access Database Engine 2016 Redistributable isn’t meant for server-side web applications, and its support ended on October 14, 2025 (notes on the Access Database Engine (external site)).
Is it enough to put the file in OneDrive or SharePoint?
No. Microsoft advises against opening an Access database from those locations. If more than one person opens the file from SharePoint, multiple copies might be created and unexpected behavior might occur (ways to share an Access database (external site)).
What about record retention requirements?
Clarify that with your tax advisor before the switch, because the answer depends on your data. Bring your inventory: which tables matter for tax purposes, who maintains them and how changes have been traceable so far. Until you have an answer, leave the old database unchanged.
What does it cost to replace an Access database?
That depends mainly on how much logic sits in forms, reports and VBA and which data types need their own solution. For market figures on a first module, see our cost guide How much does custom software development cost?. You’ll find our own packages under pricing and packages for custom business software.