0

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ronnie1001
  • 1
  • 1
  • 1
    You might have more luck adding the tables (not the views) as linked tables and trying to update them in the Access db. – the Ben B Feb 26 '20 at 16:14
  • 1
    [Note](https://support.office.com/en-us/article/create-an-access-project-89c48da0-55a4-45d4-9ee5-95f67383d4cb): Access 2010 is the last version of Access that supports working with Access project (.adp) files. Additionally, it works best with SQL Server 2008 or earlier. And this SQL Server version has past its [end of life support](https://learn.microsoft.com/en-us/support/sql/end-support-sql-server-2008). Consider re-tooling your products and workflow. – Parfait Feb 26 '20 at 16:34

1 Answers1

0

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • How do you link to the Views without using some kind of Connection String? This isn't a homebrew on my desktop, it's a commercial system that has to get through all the SQL Server logon security. I have to use "TrustedConnection=Yes", and if I do that then the View is read-only. I shall try using The Ben B's suggestion of linking to the base tables instead of the Views, since most of the Views just seem to be taking a selection of fields from their respective single base tables - although that itself is a data visibility security issue, but as the app is only for internal use it should be ok. – Ronnie1001 Feb 28 '20 at 11:10
  • OK, if I link the base tables using "TrustedConnection=Yes", then they are editable in an Access query window. The next issue is whether Access will allow me to use them as the form DataSource (for example, PassThrough queries can't be used as a form DataSource), and then I will be debating whether to keep strict Hungarian notation by using the "tbl" prefix or to give the linked tables a "vw" prefix to minimise the code changes... – Ronnie1001 Feb 28 '20 at 11:27
  • Your re-link code, or using linked table manager requires a connection string. But no need for connection strings in your code. The point that you don't in "general" as a result need connection strings "all over the place" in your code is the point here. Both links to sql tables, and links to views are read/write and both can be used as a direct data source for the forms. (so again, no connection strings required in your form code). When you link to a view, you are prompted for the PK. If your re-link code creates links to views, then an extra step of setting the PK is required. – Albert D. Kallal Feb 28 '20 at 20:32
  • There is zero benefit to basing a form on a access client side query, or basing such forms on a server side view (waste of developer time). Just link to the tables, and then base the forms on that linked table. For a sub form, you could used a view with a sort, or even a client side query for sorting when required. For reports client side sql queries are fine if one table. For multiple SQL joins, then move the query to a server side view and base the report on that view. Views will respect the "where" clause of the open form, or open report. Views perform as good as pass-trough queries. – Albert D. Kallal Feb 28 '20 at 20:40
  • as for changing the notation and table names? Why? Keep them the same as they were before, and thus all of the existing VBA code will tend to work as before with the fewest changes possible. If you change table names then a truckload of sql and queries will now be broken. Your goal is to change as little as possible, and every change in table names etc. will cost you time and efforts. – Albert D. Kallal Feb 28 '20 at 20:44
  • And dump the use of schema-binding. I see zero reason for adopting or introducing that into this mix - it is of no use or of any kind of advantage here. It is not required. I do recommend you stick with ADO, but if this was a non adp project migration then I would recommend that you continue to use DAO if that is what the original application was using. In your case, the code will have been ADO - so continue to use that data object in your VBA code. Again, little changes should be required to that VBA code. The linked tables will already have a connection. – Albert D. Kallal Feb 28 '20 at 20:54