Forms bound to updateable ADO recordsets are not updateable when the source includes a JOIN

Question

I'm developing an application in Access 2007. It uses an .accdb front end connecting to an SQL Server 2005 backend. I use forms that are bound to ADO recordsets at runtime. For the sake of efficiency, the recordsets usually contain only one record, and are queried out on the server:

Public Sub SetUpFormRecordset(cn As ADODB.Connection, rstIn As ADODB.Recordset,      rstSource As String)
Dim cmd As ADODB.Command
Dim I As Long

Set cmd = New ADODB.Command

cn.Errors.Clear

'   Recordsets based on command object Execute method are Read Only!

With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = rstSource
End With

With rstIn
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic                           'Check the locktype after   opening; optimistic locking is worthless on a bound
End With                                                    ' form, and ADO might open optimistically without firing an error!

rstIn.Open cmd, , adOpenKeyset, adLockPessimistic           'This should run the query on the server and return an updatable recordset

With cn
    If .Errors.Count <> 0 Then
        For Each errADO In .Errors
            Call HandleADOErrors(.Errors(I))
            I = I + 1
        Next errADO
    End If
End With
End Sub

rstSource (the string containg the TSQL on which the recordset is based) is assembled by the calling routine, in this case from the Open event of the form being bound:

Private Sub Form_Open(Cancel As Integer)
Dim rst As ADODB.Recordset
Dim strSource As String, DefaultSource as String
Dim lngID As Long

lngID = Forms!MyParent.CurrentID

strSource = "SELECT TOP (100) PERCENT dbo.Customers.CustomerID,   dbo.Customers.LegacyID,  dbo.Customers.Active, dbo.Customers.TypeID, dbo.Customers.Category, " & _
"dbo.Customers.Source, dbo.Customers.CustomerName, dbo.Customers.CustAddrID, dbo.Customers.Email, dbo.Customers.TaxExempt, dbo.Customers.SalesTaxCode, " & _
"dbo.Customers.SalesTax2Code, dbo.Customers.CreditLimit, dbo.Customers.CreationDate, dbo.Customers.FirstOrder, dbo.Customers.LastOrder, " & _
"dbo.Customers.nOrders, dbo.Customers.Concurrency, dbo.Customers.LegacyLN, dbo.Addresses.AddrType, dbo.Addresses.AddrLine1, dbo.Addresses.AddrLine2, " & _
"dbo.Addresses.City, dbo.Addresses.State, dbo.Addresses.Country, dbo.Addresses.PostalCode, dbo.Addresses.PhoneLandline, dbo.Addresses.Concurrency " & _
"FROM dbo.Customers INNER JOIN " & _
"dbo.Addresses ON dbo.Customers.CustAddrID = dbo.Addresses.AddrID "
strSource = strSource & "WHERE dbo.Customers.CustomerID= " & lngID

With Me                             'Default is Set up for editing one record
    If Not Nz(.RecordSource, vbNullString) = vbNullString Then
        If .Dirty Then .Dirty = False   'Save any changes on the form
        .RecordSource = vbNullString
    End If

    If rst Is Nothing Then          'Might not be first time through
        DefaultSource = .RecordSource
    Else
        rst.Close
        Set rst = Nothing
    End If
End With

Set rst = New ADODB.Recordset
Call setupformrecordset(dbconn, rst, strSource)    'dbconn is a global variable

With Me
    Set .Recordset = rst
End With

End Sub

The recordset that is returned from setupformrecordset is fully updateable, and its .Supports property shows this. It can be edited and updated in code.

The entire form, however, is read only, even though it's .AllowEdits and .AllowAdditions properties are both true. Even the fields from the right hand side (the 'many' side) cannot be edited.

Removing the INNER JOIN clause from the TSQL (restricting strSource to one table) makes the form fully editable.

I've verified that the TSQL includes priimary key fields from both tables, and each table includes a timestamp field for concurrency.

I tried changing the .CursorType and .CursorLocation properties of the recordset to no avail.

What am I doing wrong?

Answer 1

I think you want to set the Unique Table dynamic property on the recordset.

Answer 2

I had the same problem with SQL with a JOIN operation inside.

Maybe you can see see this link:

http://office.microsoft.com/en-gb/access-help/edit-data-in-a-query-HA010097876.aspx

Some queries are not updatable.

So you solved the problem?