Forms bound to updateable ADO recordsets are not updateable when the source includes a JOIN
- by Art
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?
