Updating Linked SQL Server Views in Access When Changing the Underlying Table Data Types

This may be a pretty niche problem, but it bit me hard recently.

I had an application with a table that included an Income field.  I defined the Income field as a money type.  It made sense at the time.  Later, I learned that the users needed to be able to enter arbitrary text in that field.  So, I switched the field from a money type to an nvarchar type.

This change allowed users to enter arbitrary text.

All was good–or so I thought.

One of my reports was based on a SQL Server VIEW rather than the underlying TABLE.  When I updated the TABLE definition in SQL Server, the VIEW definition did not update to match.  When users entered non-numeric data in the Income field, it did not show in the linked SQL Server VIEW within Access.

The Fix

Here are the steps to fix the above issue:

1. Run an ALTER VIEW command without changing the view's definition (this forces SQL Server to update the view's metadata)
2. Refresh the Access link to the SQL Server view (this forces Access to update the link's metadata)

Keep in mind that you must execute the above steps on every view that references the changed field(s).

Reproducing the Problem

Let's step through an exercise to reproduce the problem.

STEP 0. Prepare the Environment

• Create an empty database in SQL Server Management Studio (For subsequent runs, start by removing the existing sample table and view:)

-- STEP 0. Drop View/Table to reset
--          before subsequent runs
DROP VIEW dbo.MyView;
DROP TABLE dbo.MyTable;

STEP 1. Create Table and View

• Run the following T-SQL to create a sample table and view:

-- STEP 1. Create the Table and View
CREATE TABLE dbo.MyTable (
    ID int IDENTITY(1,1) PRIMARY KEY,
    FirstName varchar(30),
    LastName nvarchar(50),
    Income money
);
GO 

CREATE VIEW dbo.MyView AS
SELECT *, FirstName + ' ' + LastName AS FullName
FROM dbo.MyTable;
GO

Note that in SSMS, the data types between the two tables are in sync:

STEP 2. Insert a test record

• Insert a test record and observe it in the table and view

-- STEP 2. Insert a test record
INSERT INTO dbo.MyTable (FirstName, LastName, Income)
VALUES ('John', 'Doe', 30000)

--  (It shows correctly in both the table and view)
SELECT * FROM dbo.MyTable
SELECT * FROM dbo.MyView

STEP 3. Change table data type

-- STEP 3. Change data type of an underlying table field
ALTER TABLE dbo.MyTable
ALTER COLUMN Income nvarchar(20)

Note that the view's column definitions are out of sync with the underlying table's definitions within SSMS:

STEP 4. Insert a second test record

• Insert a second test record
• Observe it in SSMS
• Observe it in Microsoft Access

-- STEP 4. Insert a second test record
INSERT INTO dbo.MyTable (FirstName, LastName, Income)
VALUES ('Jane', 'Buck', '$200K')

--  (Both records show correctly 
--   in both the table and view in SSMS)
SELECT * FROM dbo.MyTable;
SELECT * FROM dbo.MyView;

STEP 5. Run ALTER VIEW to refresh metadata

• Let SSMS generate an ALTER VIEW statement for dbo.MyView
• Execute the ALTER VIEW statement to refresh the MyView metadata

-- STEP 5. Run an ALTER VIEW without changing the
--          view's definition to force it to update
--          its underlying field data values
ALTER VIEW dbo.MyView AS
SELECT *, FirstName + ' ' + LastName AS FullName
FROM dbo.MyTable;

At this point in the process, the linked view in Access is still not right.

STEP 6. Refresh the Access link to the view

Finally, we need to refresh the link to the view in Access:

After that, the linked view now displays non-numeric values in the Income field:

Image by wsyperek from Pixabay