> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Updating Linked SQL Server Views in Access When Changing the Underlying Table Data Types
- URL: https://nolongerset.com/update-view-field-type-change/
- Published: 2023-03-02T04:59:00.000Z
- Updated: 2026-05-08T12:51:19.000Z
- Description: If you change the data type of a table field in SQL Server, follow these steps to be sure all related views are updated in Microsoft Access.
- Author: Mike Wolfe
- Tags: SQL Server, #Import 2026-05-20 02:59

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:)*

```sql
-- 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:

```sql
-- 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image.png)

### STEP 2\. Insert a test record

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

```sql
-- 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
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-1.png)

Looking good so far.

### STEP 3\. Change table data type

```sql
-- 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-2.png)

### STEP 4\. Insert a second test record

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

```sql
-- 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;
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-4.png)

The non-numeric "`$200K`"value appears in both the table and view in SSMS.

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-5.png)

In Microsoft Access, the $200K is missing from the linked view, even though it appears in the linked table.

### 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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-6.png)

```sql
-- 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;
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-7.png)

The view's data types now match the underlying table.

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-8.png)

### STEP 6\. Refresh the Access link to the view

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-9.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/03/image-10.png)

Everything is back to normal in Access.

*Image by [wsyperek](https://pixabay.com/users/wsyperek-148092/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=257490) from [Pixabay](https://pixabay.com//?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=257490)*