Read/Write SQL Server views in MS Access using SCHEMABINDING

Question

I am having to redevelop an MS Access 2003/2010 ADP project, that uses SQL Server views as the RecordSource for all its forms, into an MS Access 2016 ACCDB.

I have tried using pass through queries to get the data and this works fine for readonly columns, however when I want to change the value in one of the bound columns, it says that the RecordSet is not updateable, which is what you might expect if using a view.

But I have now read that if you define the view with SCHEMABINDING like this:

ALTER VIEW [dbo].[vwQuote_MinibusesDetails]
WITH SCHEMABINDING
AS
    SELECT ...

and add a UNIQUE CLUSTERED INDEX like this:

CREATE UNIQUE CLUSTERED INDEX CIX_vwQuote_MinibusesDetails
ON vwQuote_MinibusesDetails (txtQuoteNo, txtVersion, txtVehicleNo);

and then add the view to your project as a DSN-less TableDef like this

stConnect = "ODBC;Driver=SQL Server;Server=" & SERVER_NAME & ";Database=" & APP_DATABASE & ";Trusted_Connection=Yes"
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td

it becomes editable as if it were a table.

However, when I open the TableDef in Access it shows all the rows and columns as if it were editable, but if I try to edit a column, it says that there is a Write Conflict with another user's changes, when I am 100% sure that there isn't because I am the only person using it.

Any ideas? (I am using Access 2010 at the moment)

Answer 1

But why do all that truckloads of work? There is zero reason to do all that extra work.

Simply bind and set the forms data source to a view. It is assumed that you will simply link all of the existing views on the client side. Access will thus see all the views as simply tables, and views (as opposed to Pass through queries) are read/write.

So, there is no need to: Have ANY connection strings in your code. If you ARE using connection strings in code, then you approach is all wrong. Just link to the views, and set the forms data source as that view.

At this point, the forms can edit data.

As for a pass-through quires to drive combo boxes? Do NOT do this. While a PT query is likely the fastest way to pull data, the access client cannot filter PT queries. So you ONLY EVER want to use a PT query for cases in which the client side does NOT have to filter the results. If you bind a combo box to a PT query, then Access needs and wants to ONLY pull the one value from that table for display. And since the access client can’t filter a PT query, then it will scan the WHOLE data source for that combo box to get the one value that is currently displayed. If you use a linked table (or even a view) to that source that drives the combo box, then the access client can filter that dataset to the ONE row. So, do NOT use a PT query for ANY case in which client side filtering is required. A linked view (or linked table) is fine in these cases.

So, just bind the forms directly to the linked table, or the linked view.

If the table has 1 million rows of data, and you say do this:

Docmd.OpenForm "frmInvoice",,,"InvoiceNum = 1234"

Access will open the form to the ONE row of data, and ONLY pull one row down the network pipe. This is despite that the form in question is bound to a table of 1 million rows. The form will load instant, and you not had to write any sql, any connection stuff, and not really do anything different then how you developed typical access applications.

Bound forms are how and why Access saves huge development dollars. If you jump to .net, then you have all kinds of tools and wizards that can help you around this issue. So in .net, you could adopt the dataset designer, or use the newer entity framework.

In Access, we don’t have this huge tool box of data designers and tools, so if you attempt to hand code, and code up the data sources for a form, then you get the worst possible outcome (you write truckloads of code, and don’t have all those cool tools for un-bound forms).

Simply link your forms to linked tables (or views) and you now have a working data bound form without any code. If you need to load the form, then use the 20+ year standard approach of the “where” clause of the open form command to open the form to the one record. The access client will ONLY pull what you put in the “where” clause.

So, to wire up a form for editing of data, ZERO code is required. The only developer efforts will then to ensure that the form does not pull un-necessary data to the client.