How to make a passthrough / passthru query editable?

Question

In the Microsoft Access 2007 with an SQL Server Backend, we usually take a linked table from the SQL Server as Form.RecordSource of an editable form for a single table data modification. A local query is used for cross tables editions that combines fields from several linked tables. The local query must be updatable itself in order to modify data on the editing form.

Now we are planning to replace all local queries by Passthrough queries in order to use native SQL Server tables directly.

I've tried to create a very simple passthru query named qrySelProductsPassThroughEditable with the following SQL string:

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

The ID field is the IDENTITY field defined as Primary Key in the SQL Server as the definition:

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

But the Datasheet returned by Access pass-through query is not editable at all. So it's not usable as .RecordSource for an editing form neither.

This is in contrary with the link that says if a passthru query contains all Primary Keys of all involved tables, the query will be editable.

Conclusion Added a Posteriori

Through the discussions below, a passthrough query in a Microsoft Access 2007 .accdb, .accde or .accdr (Access runtime) is always readonly, it's never editable. You should use it as a final list, or as the .RecordSource of a report, not for a form for which you must use a linked table, or a writable Normal Query involving linked tables for data IO.

Answer 1

In line with my comment above and the answer by Yawar, I'm not aware that Pass Through Queries are ever editable/updateable. They are editable in the sense that you can edit a save Pass Through Query object, but I don't believe it's possible for a Pass Through Query to produce an editable recordset.

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

The first method, and most popular, is to use some form of linked tables, generally ODBC linked tables. There are a variety of methods of using ODBC linked tables with MS Access but by what most developers prefer is to use DSN-Less connections that get refreshed or rebuilt (deleted and reconnected) at the time that your application starts. Be aware that when you use ODBC, you are also still using DAO. DAO is the default data access object built into MS Access and even when you don't specifically write any DAO code, MS Access is still using DAO under the hood to link your forms, reports and queries to your data source. In the case of ODBC, you actually end up having two data access layers at work, DAO and ODBC. But you can use ODBC/DAO with pretty decent performance and without writing code (other than to maintain the ODBC linked tables).

The second method is to use ADO. Contrary to popular belief, this does not mean that you have to use unbound forms. But it does mean that you have to write more code than using JET/DAO/MSAccess or DAO/ODBC/SSQL Server. You have to write code to bring in records from your database into and ADO Recordset and then use code to bind your form to that Recordset. You have to write more code to keep child forms in sync with parent forms, to insert foreign keys into child forms when new records are created, and for a variety of other things too like filtering and sorting as the form's built-in filtering and sorting options usually do not work with ADO recordsets. ADO is a great way to talk to SQL Server as it really gives you a lot of control, but because it's code intense, and because ODBC Linked Tables work so well, most developers do not recommend using ADO unless there's no other way to do what you want to do. One example of this is calling Stored Procedures. I believe Pass Through Queries can be used to call Stored Procedures but I also think there are some limitations there (such as using Parameters). I believe in most cases developers use ADO to call stored procedures. I use ADO a lot but I don't use Stored Procedures much (not yet) so I don't have a lot of information on that.

One other thing worth mentioning is that DAO with ODBC uses "lazy loading" but ADO forces you to pull all of the data which can be very time consuming and consume a lot of memory if you have > millions of rows. Or else you will need to implement some kind of paging.

My own function to create a single DSN-Less ODBC Linked table is below. If you're new to Access and new to VBA this probably won't make a lot of sense to you. The code deletes any table definition that already exists for the table you're trying to link, which is a little dangerous because I believe it could delete a local, non-linked table which you wouldn't want. The error handling in here isn't really up to speed either, but most online example code doesn't have good error handling in it because of the complications that involves. The creation of Primary Key Indexes on a linked table isn't always necessary. I just have it built into my function because I needed it one time for a specific project so now I leave it in there and use it, for better or for worse.

To make proper use of this code you really need to have a list of all your linked tables somewhere and iterate through that list and call this function for each table. This function allows you to link the table up using a different name than it's actual name in SQL Server. You also need to have a way of building a valid ODBC connection string which must be passed into this function too.

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 a few really good resources you should check out concerning 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/Choosing_between_DAO_and_ADO

Here's an example of binding a form to an ADO Recordset. It's a little misleading though because it's best to have a global connection object that stays open during the runtime of the application. This allows you to use ADO recordsets that are automatically updateable. Using this practice might also make your recordset a form level object.

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