In SQL Server Management Studio, I created both indexed and regular (non indexed) views. When I updated the data, the base table's data was changed as well. However, when I link my views to Access using VBA or using Access GUI linked tables I need to specify a index. If I do not do so the view will be read only.
Why is non indexed view read only in Access but not in SQL?
I personally found it very difficult to digest the accepted answer and work out what the problem was and how to resolve it.
Why aren't linked views editable?
Essentially, SQL Server views do not have a Primary Key (PK) and, therefore, are considered read-only (non-updateable) by MS Access because MS Access requires a unique column to use for updates.
How to edit the data in a linked view
A UNIQUE INDEX must be created in MS Access after the view has been linked.
For example, if the table is called dbo_table and the PK column in SQL is called id, create a new query and paste the following into it:
CREATE UNIQUE INDEX myPK ON dbo_table (id ASC)
The word myPK is the name of the index (this can be renamed if needed). Execute the query.
The linked view will now be updateable.
The reference documentation for MS Access CREATE INDEX is available here.
Well, first of all, when you use the Access GUI to link to a table, it NEVER prompts you for an index, it prompt you for the primary key. So let’s get that crazy and silly misinformation corrected like REAL fast, shall we?
When you link to a SQL server view, you get this prompt:
That above prompt is not asking you for a index.
The reason why access does this is because it needs a PK to update a linked table, or a linked view.
PROBLEM: SQL server does not have a PK defined for a view. And SQL server DOES NOT LET YOU SPECIFY a PK for a view. If SQL server had some way to define a PK for a view, then Access could use that information to grab the PK.
However, since views don’t have a PK on sql server, then when linking to such Views YOU THE HUMAN have to choose a PK.
Access linked tables are read only without a PK. Access linked views are read only without a PK.
However, sql server views do NOT have a PK defined.
Again: SQL server views do NOT have a PK defined.
If you create the linked view with VBA code, then no GUI prompt appears asking you for the PK. You can tell Access which column is to be “assumed” as the PK by executing a local create index command. This command DOES NOT ACTUALLY CREATE a index. It does not create one server side, and it does NOT create a actual working index on the access client side.
All the create index command does is tell access what PK column to use. So you not actually creating a index in this case.
Again: SQL server views do NOT have a PK defined.
If SQL server allowed one to create or set a PK for a view, then Access could simply use that information and know what PK to use, and then set it for you.
However (and again) SQL server does not have a PK defined for a view.
However for the access client to update a linked table, it needs a PK. When linking to a table, Access can simple ask SQL server what column is the PK. However, you cannot ask SQL server
“what is the primary key” of a view.
(however, you can for a table).
So you not creating a index in Access (it does nothing since a index on the client side of access with a linked table, or a linked view is NOT a working index and does NOTHING!!! (except provide a place to tell access what colum is the PK). It is NOT working index nor is it even a real index.
The existence of an index in Access for linked tables/views does not actually create a working index – only that some “extra” information such as if a PK exists in the table is stored when you “create” that index.
Again: Access does not create indexs for linked table. When you execute create index on a linked table A INDEX IS NOT CREATED IN THIS CASE. ONLY INFORMATION ABOUT THE TABLE OR VIEW IS STORED LOCAL IN ACCESS.
Again: you not creating a index.
Again: If sql server had a feature or option to define a PK for a view, then no doubt Access would also use that information. However, views in sql server don’t have a defined PK, and thus Access can’t use the PK, and if access can’t find a PK, then it assumes the table is read only.
The SQL management studio can often update tables without a PK, but Access has to use ODBC to connect to sql server, and ODBC has FAR LESS features then SSMS when connecting to a database.
