SQL Server

SQL Server Views: The Access Developer\'s Guide

SQL Server views work like saved queries in Access — but with important differences in how they perform, how they are updated, and how they fit into a migration strategy.

A
Anthony Passeri, MBA
••7 min read
SQL Server Views: The Access Developer\'s Guide

If you are migrating from Access to SQL Server, views are one of the first SQL Server features you will reach for. They look familiar — a saved SELECT statement you can query like a table — but they have capabilities and limitations that differ from Access saved queries in ways worth understanding.

What Is a View?

A view is a named SELECT statement stored in the database. You query it like a table:

-- Create the view
CREATE VIEW vw_ActiveCustomers AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    Email,
    City,
    State
FROM Customers
WHERE IsActive = 1;

-- Query it like a table
SELECT * FROM vw_ActiveCustomers WHERE State = 'WA';

From the caller's perspective, vw_ActiveCustomers behaves like a table. The underlying SELECT runs when you query the view.

This is directly analogous to a saved query in Access. The main difference: SQL Server views are stored in the database alongside the tables, not in a separate application file.

Why Views Matter in a Migration

When you migrate from Access to SQL Server with linked tables, your Access saved queries do not automatically become SQL Server views. They stay in the Access frontend as local queries.

That works, but it has a cost: complex queries run in Access, not on the server. The data travels across the network to Access, which filters and joins it locally. For large tables, this is slow.

Moving complex queries to SQL Server views pushes the work to the server, where it runs faster and the results (a smaller dataset) travel across the network instead of the raw data.

Migration strategy: as you migrate, identify your most-used Access queries — especially those that join multiple tables or filter large datasets — and recreate them as SQL Server views. Link those views in Access the same way you link tables.

Creating Views in SQL Server Management Studio

Two ways to create a view:

Using the GUI: In SSMS, expand your database > Views > right-click > New View. A visual designer similar to Access's query design view appears. Add tables, select columns, set criteria.

Using T-SQL:

CREATE VIEW vw_OrderSummary AS
SELECT
    o.OrderID,
    o.OrderDate,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    o.TotalAmount,
    o.Status
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= DATEADD(year, -1, GETDATE());

To modify an existing view:

ALTER VIEW vw_OrderSummary AS
SELECT
    -- updated SELECT statement
    ...

To remove a view:

DROP VIEW vw_OrderSummary;

Updatable Views

In Access, you can often edit records through a query — open the query in datasheet view, click a cell, type a new value. SQL Server views support the same thing, with conditions.

A view is updatable if:

  • It references only one base table (or the update affects only one table in a multi-table view)
  • It does not use DISTINCT, GROUP BY, HAVING, UNION, or TOP
  • It does not include aggregate functions (SUM, COUNT, AVG, etc.)
  • It does not include subqueries in the SELECT list
  • The columns being updated are not computed

Simple single-table views are almost always updatable. Complex multi-table views usually are not.

-- This view IS updatable (single table, no aggregates)
CREATE VIEW vw_CustomerContacts AS
SELECT CustomerID, FirstName, LastName, Email, Phone
FROM Customers
WHERE IsActive = 1;

-- UPDATE through the view works
UPDATE vw_CustomerContacts
SET Phone = '555-0199'
WHERE CustomerID = 42;

For non-updatable views, use INSTEAD OF triggers to define custom insert/update/delete behavior — but that is an advanced topic for another post.

Indexed Views: Materialized Results

Standard views are virtual — the SELECT runs every time you query the view. For complex views that are queried frequently, this can be slow.

SQL Server supports indexed views (also called materialized views): the view's results are computed once and stored on disk, like a table. SQL Server keeps the stored results synchronized with the underlying tables automatically.

-- Step 1: Create the view with SCHEMABINDING
CREATE VIEW vw_DailySalesSummary
WITH SCHEMABINDING
AS
SELECT
    CAST(OrderDate AS DATE) AS SaleDate,
    COUNT_BIG(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSales
FROM dbo.Orders
GROUP BY CAST(OrderDate AS DATE);

-- Step 2: Create a unique clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IX_DailySalesSummary
ON vw_DailySalesSummary (SaleDate);

After this, queries against vw_DailySalesSummary read from the stored results instead of re-aggregating the Orders table every time. For a reporting view that summarizes millions of rows, this is a dramatic performance improvement.

Requirements for indexed views:

  • Must use WITH SCHEMABINDING (the view is bound to the underlying table structure)
  • Must have a unique clustered index
  • Several T-SQL features are not allowed (OUTER JOIN, subqueries, non-deterministic functions like GETDATE())

Views vs. Stored Procedures for Reporting

Access developers sometimes ask: should I use a view or a stored procedure for my reports?

Use a view when:

  • You need to query the result like a table (join it to other tables, filter it with WHERE)
  • You want to link it in Access as a linked table
  • The logic is a SELECT with no parameters

Use a stored procedure when:

  • You need to pass parameters (date ranges, customer IDs, status filters)
  • The logic involves multiple steps, conditional logic, or temporary tables
  • You need to return multiple result sets

Views and stored procedures are complementary. A common pattern: a view that defines the base dataset, and a stored procedure that accepts parameters and queries the view.

Naming Conventions

A consistent naming convention makes a SQL Server database much easier to navigate:

  • Prefix views with vw_: vw_ActiveCustomers, vw_OrderSummary, vw_MonthlyRevenue
  • Use descriptive names that describe what the view returns, not how it works
  • Group related views with a common prefix: vw_Sales_, vw_Customer_, vw_Inventory_

Linking Views in Access

SQL Server views appear as tables when you link them in Access via ODBC. The process is identical to linking a table:

  1. External Data > New Data Source > From Other Sources > ODBC Database
  2. Connect to your SQL Server database
  3. Select the views you want to link (they appear in the same list as tables)

One important difference: Access needs a unique key to enable editing through a linked view. If the view is updatable and you want to edit records through it in Access, you may need to specify the primary key when linking — Access will prompt you if it cannot detect one automatically.

The Migration Payoff

Moving your Access saved queries to SQL Server views is one of the highest-leverage steps in a migration. It shifts query execution from the client (Access) to the server (SQL Server), reduces network traffic, and makes your business logic portable — any application that connects to SQL Server can use the same views.

It also makes the eventual transition away from Access easier. When your queries live in SQL Server views rather than Access query objects, the Access frontend becomes thinner and more replaceable.

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.

Replacing Access queries with SQL Server views as part of a migration? Schedule a free call and get a query conversion plan.

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

#SQL Server#views#T-SQL#migration#Access migration

Keep reading

More on SQL Server