2

I am having some trouble with updates to an SQL Server View through MS Access. The set of tables used for this is built off of a base table. This base table is of this format.

Id    int (not-nullable; auto-assigned)
A1    varchar(50) (nullable)
A2    varchar(50) (nullable)
B1    varchar(50) (nullable)
B2    varchar(50) (nullable)
C1    varchar(50) (nullable)
C2    varchar(50) (nullable)

One row on this table is updated by multiple groups of users in our company. For instance, user group "A" updates columns "A1" and "A2", user group "B" updates columns "B1" and "B2", and so forth. However, we also want to prevent user group "A" from updating the columns of user group "B". To accomplish this, I set up a view containing the columns appropriate for each user group. For instance, the view for user group "A" would only contain the columns "Id", "A1", and "A2". Then I set the "Bind To Schema" option on the views in SSMS to "Yes", and I set up a unique, clustered index on the "Id" column on each of the views. In MS Access, I connect to these views as linked tables using an ODBC connection. When I open the tables in MS Access in design view and check the indexes, it does properly identify the "Id" column as the primary key.

Here is where the trouble comes in: When I try to update a record through MS Access in one of the views, sometimes the update runs instantly, but sometimes the update times out. Here is the error that I get.

Timeout Error Message

"SM_Notes_Bridge" is the actual name of one of my views. Almost all previous answers that I can find online say to increase the amount of time before the update times out in MS Access, which seems like it is not a solution for my problem as the update either runs instantly or times out. There is no middle ground.

Another note is that I am currently the only one using this base table and these views. Also, important systems are developed around that base table structure, so changing its structure will take a lot of convincing.

Nathan M.
  • 286
  • 1
  • 9
  • 1
    Can you please try to describe shortly . No one will spare their time to read this all . – Mahesh.K Feb 13 '18 at 14:22
  • 1
    Sure. I'll spend some time trying to boil it down. – Nathan M. Feb 13 '18 at 14:26
  • thanks .. sounds great -:) – Mahesh.K Feb 13 '18 at 14:27
  • 1
    Things to try: 1) if you directly link the base table in Access, and edit records there, does the timeout ever happen? 2) Remove the unique indexes on the views. They are not really necessary, you only have to tell Access that `Id` is the PK when linking the views. – Andre Feb 13 '18 at 14:43
  • I would do your update in sql server instead of pass through queries. This means creating a stored procedure that you would then execute from Access. This will start to build layers into your application which at some point you will be grateful because if this app gets large you will want a different front end. If your sql is scattered through the application this is extremely difficult. If the data work is in the database, or at least a data layer, this becomes a lot simpler. – Sean Lange Feb 13 '18 at 14:46
  • @Andre I tried your suggestion (1). When I linked to the base table and tried to update it, I never got a timeout error. I have not tried your second suggestion yet. I will add a comment when I get time to try it. – Nathan M. Feb 13 '18 at 15:00

1 Answers1

2

By creating an unique index on a schema bound view, you're creating an indexed view, also called a materialized view.

A relevant property of indexed views:

When executing DML on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those referenced indexed views will have to be updated as well. As a result, DML query performance can degrade significantly, or in some cases, a query plan cannot even be produced (MSDN).

Thus, creating multiple indexed views on a table that is updated often is a big no-no! Review this MSDN page for further explanation when and when not to use an indexed view. Every insert and update will have to propagate to all the indexed views, and will cause locks on those views as well.

Drop the indexes on ALL views on that table. As far as you've told me, there's no reason at all to use indexed views and they will hurt performance in a major way when executing updates. Even if that didn't fix this issue, it will improve performance.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • This could very well be my issue. For the sake of simplicity in the post, I only listed a few views, but there are actually 15 views to this table, each with 2 indexes. I've left the office for the day, but I will definitely try this first thing tomorrow morning and come back to accept this answer if it fixes it. – Nathan M. Feb 13 '18 at 22:50
  • This solution seems to have solved my problem. Thanks so much! I still had to force MS Access to treat a column as a primary key, but I removed the indexes and everything seems to work fine. For anyone curious, you can use the answer at the following link to force Access to treat a column as a primary key. https://stackoverflow.com/questions/30915181/how-to-set-primary-key-when-linking-with-createtabledef – Nathan M. Feb 15 '18 at 14:22