Stored Procedure ADO recordset is read only on a form

[StrikeEagleII]

I finished creating a stored procedure for SQL Server 2008 that produces a recordset I'd like to use as the recordset for a form, but it's coming up read only.

Private Sub UpdateFormRecordSource(Optional FormOpen As Boolean = 0)
    
    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command

    Dim strSQL As String
    With cmd
        .ActiveConnection = gCon
        .CommandType = adCmdStoredProc
        .CommandText = "[GOCS].[usp_UpdateTIInput]"
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@FormOpen", adBoolean, adParamInput, , FormOpen)
        If Nz(Me.cboEffectivity) <> "" Then .Parameters.Append .CreateParameter("@Aircraft", adVarChar, adParamInput, 8, Me.cboEffectivity)
        If Nz(Me.cboTOLNum) <> "" Then .Parameters.Append .CreateParameter("@TOLNum", adVarChar, adParamInput, 4, Me.cboTOLNum)
        If Nz(Me.cboProgramFilter) <> "" Then .Parameters.Append .CreateParameter("@AircraftProgram", adVarChar, adParamInput, 8, Me.cboProgramFilter)
        If Nz(Me.cboATAFilter) <> "" Then .Parameters.Append .CreateParameter("@ATAFilter", adInteger, adParamInput, , Me.cboATAFilter)
        If Nz(Me.txtTitleFilter) <> "" Then .Parameters.Append .CreateParameter("@TitleFilter", adVarChar, adParamInput, 255, Me.txtTitleFilter)
        If Nz(Me.txtClosureCriteriaFilter) <> "" Then .Parameters.Append .CreateParameter("@ClosureCriteria", adVarChar, adParamInput, 255, Me.txtClosureCriteriaFilter)
        If Nz(Me.cboTIFocalFilter) <> "" Then .Parameters.Append .CreateParameter("@TIFocal", adInteger, adParamInput, , Me.cboTIFocalFilter)
        If Nz(Me.cboTOLTypeFilter) <> "" Then .Parameters.Append .CreateParameter("@TOLType", adVarChar, adParamInput, 4, Me.cboTOLTypeFilter)
        If Nz(Me.cboTIStatusFilter) <> "" Then .Parameters.Append .CreateParameter("@TIStatus", adInteger, adParamInput, , Me.cboTIStatusFilter)
        If Nz(Me.cboGroupFilter) <> "" Then .Parameters.Append .CreateParameter("@EngGroup", adInteger, adParamInput, , Me.cboGroupFilter)
        If Nz(Me.chkOutForClosureFilter) <> False Then .Parameters.Append .CreateParameter("@OutForClosure", adBoolean, adParamInput, , 1)
    End With
    With rst
        .Open cmd, , adOpenDynamic, adLockOptimistic
        .Properties("Unique Table") = "GOCS.tblTOLs"
    End With
    
    Set Me.Recordset = rst
    rst.Close: Set rst = Nothing
    Set cmd = Nothing
    'Me.DataEntry = False
    
    If Nz(Me.cboEffectivity) <> "" Then
        Me.sfmEffectiveOn.Form.RecordsetClone.FindFirst "fkAircraft='" & Me.cboEffectivity & "'"
        If Not Me.sfmEffectiveOn.Form.RecordsetClone.NoMatch Then
            Me.sfmEffectiveOn.Form.Recordset.Bookmark = Me.sfmEffectiveOn.Form.RecordsetClone.Bookmark
        End If
    End If
    
    
End Sub

gCon is defined earlier:

Public Const gstrCon As String = _
           "DRIVER=SQL Server Native Client 10.0;" _
         & "Server=A3992756\SQLEXPRESS;" _
         & "Database=FTEODATA;" _
         & "Trusted_Connection=Yes;" _
         & "MARS Connection = TRUE;"
         
Public gCon As ADODB.Connection

Public Function GetDatabaseConnection() As Boolean
On Error GoTo GetDatabaseConnectionError
   
    Set gCon = New ADODB.Connection
    gCon.ConnectionString = gstrCon
    gCon.CursorLocation = adUseClient
    gCon.Open
    GetDatabaseConnection = True
    Exit Function

ResumeGetDatabaseConnection:
    On Error Resume Next
    GetDatabaseConnection = False
    Set gCon = Nothing
    Exit Function

GetDatabaseConnectionError:
    Debug.Print Err.Number & " " & Err.Description
    If Err.Number = -2147467259 Then MsgBox "Cannot connect to database server! Double check network connection!", vbCritical + vbOKOnly, "Error Connecting. . . "
    Resume ResumeGetDatabaseConnection

End Function

The stored procedure is based on the tables tblTOLs, tblTOLAircraftJoin and tlkpTOLTypes. tblTOLAircraftJoin is there only for filtering purposes. The recordset doesn't actually return any fields from this table. tlkpTOLTypes is only a lookup table to be used when the form is in a read only mode. I only want to edit data in tblTOLs. Here's the stored procedure:

CREATE proc [GOCS].[usp_UpdateTIInput]
			@FormOpen bit = 0,
			@Aircraft nvarchar(8) = null,
			@TOLNum nvarchar(4) = null,
			@AircraftProgram nvarchar(8) = null,
			@ATAFilter int = -1,
			@TitleFilter nvarchar(255)=null,
			@ClosureCriteria nvarchar(255) = null,
			@TIFocal int = -1,
			@TOLType nvarchar(4) = null,
			@TIStatus int = -1,
			@EngGroup int = -1,
			@OutForClosure bit = 0,
			@debug bit = 0
AS
Begin
-- This proc returns the data needed to populate frmTIInput.
-- If the argument FormOpen is not false, then this SPROC will always return an empty recordset (to prevent all records being
-- returned when the form is opened
    
    Declare @strSQL nvarchar(4000)
    
	SELECT @strSQL = N'SELECT DISTINCT t.pkTOLID, t.fkTOLType, t.SubjectTitle, t.TOLNum, t.fkATAChapter, t.fkReasonID, ' +
              N't.fkTIFocal, t.fkEngAuthor, t.fkTIStatus, t.SortNum, t.fkProgram, t.ActionOwner, t.ActionDescription, ' +
              N't.ActionDueDate, t.fkTIGroup, t.FirstPlanCloseDate, t.ClosureCriteria, tt.TOLTypeDes, tt.SortOrder ' +
              N'FROM GOCS.tlkpTOLTypes tt INNER JOIN (GOCS.tblTOLs t LEFT JOIN GOCS.tblTOLAircraftJoin taj ON t.pkTOLID = taj.fkTOLID) ON tt.pkTOLTypes = t.fkTOLType '

    SELECT @strSQL = @strSQL + N' WHERE 0 = @FormOpen'

    If @Aircraft is not null
		SELECT @strSQL = @strSQL + N' AND (taj.fkAircraft = @Aircraft)'
        
    If @TOLNum is not Null
        SELECT @strSQL = @strSQL + N' AND (t.TOLNum = @TOLNum)'
    
    If @AircraftProgram is not null
		SELECT @strSQL = @strSQL + N' AND (t.fkProgram= @AircraftProgram)'
    
    If @ATAFilter <> -1
		SELECT @strSQL = @strSQL + N' AND (t.fkATAChapter = @ATAFilter)'
		    
    If @TitleFilter is not null 
        SELECT @strSQL = @strSQL + N' AND (t.SubjectTitle LIKE ''%'' + @TitleFilter + ''%'')' 
    
    If @ClosureCriteria is not null
		SELECT @strSQL = @strSQL + N' AND (t.ClosureCriteria LIKE ''%'' + @ClosureCriteria + ''%'')'
    
    If @TIFocal <> -1
		SELECT @strSQL = @strSQL + N' AND (t.fkTIFocal = @TIFocal)'
    
    If @TOLType is not Null 
		SELECT @strSQL = @strSQL + N' AND (t.fkTOLType = @TOLType)'
    
    If @TIStatus  <> -1
		SELECT @strSQL = @strSQL + N' AND (t.fkTIStatus = @TIStatus)'
    
    If @EngGroup <> -1 
		SELECT @strSQL = @strSQL + N' AND (t.fkTIGroup = @EngGroup )'
    
    If @OutForClosure <> 0
		SELECT @strSQL = @strSQL + N' AND (taj.blnOutForClosure <> 0)'
    
    SELECT @strSQL = @strSQL + N' ORDER BY t.SortNum;'
    
	if @debug = 1 print @strSQL;
	
	exec sp_executesql @strSQL, N'@FormOpen bit = 0,
			@Aircraft nvarchar(8),
			@TOLNum nvarchar(4) = Null,
			@AircraftProgram nvarchar(8) = Null,
			@ATAFilter int = -1,
			@TitleFilter nvarchar(255) = Null,
			@ClosureCriteria nvarchar(255) = Null,
			@TIFocal int = -1,
			@TOLType nvarchar(4) = Null,
			@TIStatus int = -1,
			@EngGroup int = -1,
			@OutForClosure bit = 0',
			@FormOpen,
			@Aircraft,
			@TOLNum,
			@AircraftProgram,
			@ATAFilter,
			@TitleFilter,
			@ClosureCriteria,
			@TIFocal,
			@TOLType,
			@TIStatus,
			@EngGroup,
			@OutForClosure
	
end

I believe my connection string is correct because I can create a linked table to tblTOLs and can open it directly and edit it fine. Your help is appreciated in advance.

 

[StrikeEagleII]

Sweet monkeys!! It turned out the problem was that I didn't include the primary key of one of the join tables. As soon as I modified the stored procedure to add that field (tt.pkTOLtypes) to the query output, it was updateable.