How to make passthrough / passthru request editable?

Question

In Microsoft Access 2007 with a SQL Server back-end, we usually take a linked table with SQL Server as an Editable Form Form.RecordSource to modify a single data table. A local query is used for crosstabs that combine fields from several related tables. The local request must be updated itself in order to change the data in the editing form.

Now we plan to replace all local queries with Passthrough queries to directly use SQL Server native tables.

I tried to create a very simple passthru query called qrySelProductsPassThroughEditable with the following SQL line:

SELECT dbo.Products.ID, dbo.Products.Name FROM dbo.Products; 

ID field - IDENTITY field, defined as the primary key of SQL Server, as a definition:

 CREATE TABLE [dbo].[Products]( [ID] [int] IDENTITY(1,1) NOT NULL, .... ) 

But the data table returned by the Access pass-through method is not edited at all. Therefore, it cannot be used as a .RecordSource for an edit form.

This contradicts the link , which states that if the passthru query contains all the primary keys of all the tables involved, the query will be editable.

Conclusion Posted by Posteriori

In the discussions below, the end-to-end query in Microsoft Access 2007.accdb, .accde, or .accdr (Access runtime) is always read-only, it is never edited. You should use it as a final list or as a .RecordSource report, and not for the form for which you should use a linked table, or a regular regular record-related query, using linked tables to enter data.

Answer 1

According to my comment above and Yavar's answer, I don't know that end-to-end queries are always available for editing / updating. They are edited in the sense that you can edit the Skip Through Query object, but I don’t think it is possible for Skip Through Query to create an editable record set.

There are basically two ways to connect Access to a non-Access data source.

The first method and the most popular one is to use some form of related tables, mainly related to ODBC tables. There are many ways to use ODBC-related tables with MS Access, but most developers prefer to use DSN-Less connections, which are updated or rebuilt (deleted and reconnected) at the time your application starts. Keep in mind that when using ODBC, you are also using DAO. DAO is the default access object built into MS Access, and even if you do not write any DAO code, MS Access still uses the DAO under the hood to link your forms, reports and queries with your data source. In the case of ODBC, you actually get two levels of data access, DAO and ODBC. But you can ODBC / DAO with pretty decent performance and without writing code (other than supporting related ODBC tables).

The second method is to use ADO. Contrary to popular belief, this does not mean that you need to use unrelated forms. But that means you need to write more code than using JET / DAO / MSAccess or DAO / ODBC / SSQL Server. You must write code to enter records from your database and into ADO Recordset, and then use the code to bind the form to this record set. You must write more code to synchronize child forms with parent forms, insert foreign keys into child forms when creating new records, and possibly for many other things, such as filtering and sorting. ADO is a great way to talk to SQL Server, because it really gives you a lot of control, but since it is very rich in code, and because ODBC Linked Tables work so well, most developers do not recommend using ADO if there is no other way to do what You want to do. One example of this is calling stored procedures. I believe that end-to-end queries can be used to call stored procedures, but I also think that there are some restrictions (for example, the use of parameters). I believe that in most cases, developers use ADO to call stored procedures. I use ADO a lot, but I don't use a lot of stored procedures (not yet), so I don't have much information about this.

My own function for creating one DSN-ODBC related related table is below. If you're new to Access and new to VBA, this probably won't make much sense to you. The code deletes any table definition that already exists for the table you are trying to associate, which is a bit dangerous because I believe that it can delete a local, unrelated table that you don't want. Error handling is not very efficient here, but most online code examples do not have good error handling due to the complications involved. Creating primary key indexes in a linked table is not always necessary. I just included it in my function because I needed it once for a specific project, so now I leave it there and use it, better or worse.

To use this code correctly, you really need to have a list of all your related tables somewhere and iterate over this list and call this function for each table. This feature allows you to associate a table using a different name than the actual name in SQL Server. You should also have a way to build a valid ODBC connection string, which should also be passed to this function.

 Private Sub LinkODBCTable(sSourceTableName As String, _ sLocalTableName As String, _ sPrimaryKeyField As String, _ sConString As String) Dim dbCurrent As DAO.Database Dim tdfCurrent As DAO.TableDef Set dbCurrent = DBEngine.Workspaces(0).Databases(0) On Error Resume Next 'Be Careful, this could delete a local, non-linked table. dbCurrent.TableDefs.Delete sLocalTableName If Err.Number <> 0 Then If Err.Number = 3011 Then 'Table does not exist Else MsgBox "Error in LinkODBCTable" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description End If Err.Clear End If On Error GoTo 0 Set tdfCurrent = dbCurrent.CreateTableDef(sLocalTableName) tdfCurrent.Connect = sConString tdfCurrent.sourceTableName = sSourceTableName dbCurrent.TableDefs.Append tdfCurrent On Error Resume Next If sPrimaryKeyField <> "" Then dbCurrent.Execute "CREATE INDEX __UniqueIndex ON [" & sLocalTableName & "] (" & sPrimaryKeyField & ")", dbFailOnError If Err.Number <> 0 Then If Err.Number = 3283 Then 'Primary Key Already Exists Else MsgBox "Error in LinkODBCTable" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description End If Err.Clear End If End If Set tdfCurrent = Nothing Set dbCurrent = Nothing End Sub 

There are some really good resources you should check out regarding DAO, ADO, Pass Through Queries, SQL Server, etc:

http://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx
http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO

Here is an example of binding a form to an ADO recordset. This is a bit misleading, because it is best to have a global connection object that remains open at runtime. This allows you to use automatic updates to ADO record sets. Using this practice can also make your recordset a form level object.

http://msdn.microsoft.com/en-us/library/office/bb243828%28v=office.12%29.aspx

Answer 2

There is an easier documented way to open any SQL Server Select query (table, view or sql-select with many related tables) in the MsAccess query window and be editable / updatable:

Open the access request window and enter your SQL query. Replace the table name (s) with the full ODBC string on SQL Server inside the square brackets, with the next dot and the name of the schema and table, as in the following example:

Before:

 SELECT SOH.SalesOrderID, SOH.OrderDate FROM Sales.SalesOrderHeader as SOH 

After:

 SELECT SOH.SalesOrderID, SOH.OrderDate FROM [ODBC;Driver=SQL Server;Server=myServer;Database=AdventureWorks2012;Trusted_Connection=Yes;MarsConn=yes;].Sales.SalesOrderHeader as SOH 

Now the request is updated:

Notes:

• Not every SQL statement makes a table or view updatable. For limitations and limitations, see the "Updated Views" section in CREATE VIEW (Transact-SQL) ( https://msdn.microsoft.com/en-us/library/ms187956.aspx ).
• The base table that you want to update in Access must have a timestamp or RowVersion column.

Answer 3

End-to-end query result sets are not editable, but Access queries based on linked tables are definitely.

Answer 4

Yes, its true: "The second method is to use ADO" that the LinkMasterFields and LinkChildFields properties do not work in multi-format and the ADO recordset does not work in the Access 2013 report, so I use a pass-through query, I use ADP + ADPX.accde for modeling LinkMasterFields and LinkChildFields properties in a multi-format and multi-page report.