I have an Access Project with a Microsoft SQL backend, in which I give the users several thousand records for browsing. My problems began when I took this advice to heart: "It is better to use ADO Recordsets only for retrieving data, and to use SQL stored procedures via ADO Command objects to perform insert, update, and delete operations."

I have three stored procedures: spGet, spAdd(parms), spUpdate(parms) corresponding to which I've made the Command objects: comGet, comAdd, comUpdate. I'm able to retrieve records into a recordset and use that recordset as a source for my form. I'm also able to invoke each command and change the backend table.

My problem is how to get the Add/Update stored procedures to mesh with the recordset approach? (RecordSet.AddNew and .Update methods seem clearly to clash with the stored procedure approach.)

I've been able to Add and Update by programmatically unbinding the controls in the form, gathering new values, then calling spAdd or spUpdate, then reinvoking spGet to retrieve several thousand records. But surely there must be a more efficient way. Below is some shortened code from my Form (I show only Add, but Update works similarly).

' initialize command objects and recordset, retrieve initial records
Private Sub Form_Load()
   With comAdd ' module variable for command object
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "spAdd"
      .Parameters.Append spAdd.CreateParameter("@name", adVarChar, adParamInput, 50)
   End With
       '-- SIMILAR CODE GOES HERE FOR comGet and comUpdate
   Set mRst = New ADODB.Recordset ' module level recordset
   mRst.Open comGet, , adOpenStatic ' retrieve data
   Set Me.Recordset = mRst ' link form to recordset
   Me.txtName.ControlSource = "Name" ' bind control
End Sub

' add button – unbind and blank control so new value can be gathered
Private Sub cmdAdd_Click()
   With Me.txtName
      .SetFocus
      .ControlSource = "" ' unbind control so I can assign a value
      .Text = ""
   End With
End Sub

' save button – call stored procedure and retrieve records
Private Sub cmdSave_Click()
   With comAdd
         .Parameters("@name") = Me.txtName
         .Execute , , adExecuteNoRecords
      End With
   End If
   Set mRst = comGet.Execute ' reexecute stored proc to retrieve records - again!!
   Set Me.Recordset = mRst ' relink to form recordset
   mRst.MoveLast
End Sub

As I say, this approach works, but I'm not happy having to retrieve all those records I already had everytime I make a single change to the data. Is there a better way to make stored procedures cooperate with recordsets?

Roy