Power Platform

Dataverse Explained for Access Developers

Dataverse is the database behind Power Apps. If you are migrating from Access, understanding how Dataverse tables, relationships, and security differ from Access is essential.

A
Anthony Passeri, MBA
••7 min read
Dataverse Explained for Access Developers

When Access developers start evaluating Power Apps, Dataverse is the part that takes the most adjustment. It looks like a database — tables, columns, relationships — but it behaves differently from Access in ways that matter.

This post explains Dataverse from an Access developer's perspective: what maps directly, what is different, and what you need to understand before you start migrating data.

What Dataverse Is

Dataverse is a cloud-based data platform built into Microsoft 365. It stores data in tables (Microsoft's term for what most developers call tables), enforces relationships and business rules at the platform level, and integrates natively with Power Apps, Power Automate, and Power BI.

When you build a Power App, Dataverse is the recommended backend. It handles storage, security, auditing, and API access — things you would otherwise build yourself.

Think of it as SQL Server with a lot of built-in infrastructure: row-level security, audit logs, file attachments, calculated columns, and a REST API — all configured through a UI rather than code.

Tables: Familiar but Different

Dataverse tables look like Access tables: rows and columns, primary keys, relationships. But there are important differences.

Every table has a system-generated primary key. You cannot use your own integer IDs as primary keys in Dataverse. Every row gets a GUID (a 36-character unique identifier like a1b2c3d4-e5f6-7890-abcd-ef1234567890). You can add your own autonumber column for display purposes, but the system key is always a GUID.

This matters for migration: your Access foreign keys (integer IDs) need to be remapped to GUIDs in Dataverse.

Standard tables vs. custom tables. Dataverse includes hundreds of pre-built standard tables: Account, Contact, Lead, Opportunity, Task, and many more. These are designed for common business scenarios and integrate with Dynamics 365.

For most Access migrations, you will create custom tables — tables you define yourself. Custom tables work like Access tables but live in the cloud.

Table ownership. Dataverse tables have an ownership model: Organization-owned (all users share the data) or User/Team-owned (each row is owned by a specific user or team, enabling row-level security). Access has no equivalent — all users see all data. Choose ownership type carefully; it affects security configuration significantly.

Column Types: What Maps and What Does Not

Most Access field types have a Dataverse equivalent, but the names and behaviors differ.

Access Field TypeDataverse Column TypeNotes
AutoNumberAutonumberDisplay only; system key is still a GUID
Short TextTextMax 4,000 characters
Long Text / MemoMultiline TextUp to 1,048,576 characters
Number (Integer)Whole Number
Number (Double)Decimal Number or Float
CurrencyCurrency
Date/TimeDate and Time
Yes/NoYes/No (Two Options)
LookupLookupPoints to another table's row
AttachmentFile or ImageStored in Azure Blob Storage
OLE ObjectFileOLE Objects must be converted

What does not map cleanly:

  • Hyperlink fields — store as Text in Dataverse
  • OLE Objects — must be exported and stored as File columns or in SharePoint
  • Multi-value fields — Dataverse does not support multi-value columns; normalize these into a related table

Relationships: Enforced at the Platform Level

Access relationships are defined in the Relationships window and enforced (optionally) through referential integrity settings. Developers sometimes skip referential integrity, leading to orphaned records.

Dataverse enforces relationships at the platform level. When you create a lookup column (the Dataverse equivalent of a foreign key), the relationship is always enforced. You cannot have an orphaned record.

Relationship behaviors control what happens when a parent record is deleted:

  • Cascade — delete the parent, delete all related child rows
  • Restrict — prevent deletion of the parent if child rows exist
  • Remove Link — delete the parent, set the lookup column to null in child rows
  • Referential — enforce the relationship but do not cascade

Choose carefully. Cascade delete in Dataverse is permanent and immediate — there is no undo.

Security: Role-Based, Not Password-Based

Access security is famously weak. The old workgroup security model was deprecated years ago, and most Access databases rely on network share permissions or nothing at all.

Dataverse has enterprise-grade security built in:

Security roles define what a user can do — create, read, update, delete — at the table level and the row level. A user might be able to read all Accounts but only update Accounts they own.

Business units organize users into a hierarchy. Security roles can be scoped to a business unit, so a regional manager sees only their region's data.

Column-level security restricts access to specific columns. A user might see a Contact record but not the Social Security Number column.

This is a significant upgrade from Access, but it requires planning. Before migrating, map out who needs to see what — the security model needs to be designed, not bolted on afterward.

Business Rules and Calculated Columns

In Access, validation rules and calculated fields live in the table design or in form code. In Dataverse, they live in the table itself and apply regardless of how the data is accessed.

Business rules are if/then logic applied at the table level: "If Status is Closed, make the Close Date required." They run in Power Apps forms automatically.

Calculated columns compute a value from other columns in the same row: concatenating first and last name, calculating days since last contact, deriving a status from multiple fields.

Rollup columns aggregate related rows: total order value for a customer, count of open tasks for a project.

These replace a lot of the VBA validation and calculated field logic in Access forms — and they apply consistently across every app that uses the table.

What Dataverse Does Not Have

No SQL queries. You cannot write SELECT * FROM Accounts WHERE... against Dataverse directly. You query it through the Power Apps formula language (FX), the Dataverse Web API (OData), or Power Automate connectors. This is the biggest adjustment for Access developers who think in SQL.

No stored procedures. Business logic that lives in SQL Server stored procedures needs to move to Power Automate flows or Dataverse plugins (server-side code that runs on data events).

No local file. Dataverse lives in the cloud. There is no .accdb file to back up, copy, or email. Backup and recovery are managed by Microsoft.

Licensing

Dataverse requires a Power Apps per-user or per-app license. It is not included in standard Microsoft 365 plans. This is often the first question that comes up in migration planning — and it is a legitimate cost consideration.

For small teams (under 10 users), the per-app plan ($5/user/app/month) is usually the most cost-effective. For larger teams or multiple apps, per-user ($20/user/month) makes more sense.

Factor this into your migration cost estimate. The platform cost is real, but so is the cost of maintaining aging Access infrastructure.

Is Dataverse Right for Your Migration?

Dataverse is the right choice when:

  • You want native Power Apps integration with minimal custom code
  • You need row-level security or audit trails
  • Your data will be accessed from multiple apps or platforms
  • You are already in the Microsoft 365 ecosystem

SQL Server may be a better fit when:

  • You have complex queries that need T-SQL
  • You have existing SQL Server infrastructure
  • Your team has SQL Server expertise
  • You need maximum query performance on large datasets

Many organizations use both: SQL Server for complex transactional data, Dataverse for the apps that surface that data to end users.

Understanding Dataverse before you start migrating saves significant rework. The concepts are learnable — they just require unlearning some Access assumptions first.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Making the transition from Access developer to Dataverse developer? Schedule a free call and get a learning roadmap tailored to your background.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#Dataverse#Power Apps#migration#Power Platform#Access migration

Keep reading

More on Power Platform