Updating Data in a Recordset (Stored Procedure)

[binky]

Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question:

I have a stored procedure that returns a set of records from a SQL Server and loads it into a form in my Access application. This works as intended, but I'm having trouble trying to figure out how to modify records on the form... whenever I try i get the error that says youc an't modify this data because its based on an expression.

i'm just learning stored procedures in access so i was wondering if anybody could help. the code that populates the form is below.

Basically I'm wondering if I can have the application automatically update data without having to create a 2nd procedure to push changes back to the server.

Set gcn = Nothing
    Dim sConnect As String
    sConnect = "Driver={SQL Server};Server=XXXX;Database=XXXXX;Trusted_Connect  ion=Yes;"
 
    Set gcn = New ADODB.Connection
    gcn.CursorLocation = adUseClient
    gcn.Open sConnect
 
    'On Error GoTo ExitProcedure
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    'Call doConnect
 
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
 
    With cmd
        .CommandText = "my_procedure"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@parm1", adChar, adParamInput, 15, Me.cbxData_Set)
        Set .ActiveConnection = gcn
    End With
 
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseServer
    rs.Open cmd, , adOpenStatic, adLockPessimistic
 
    Set rs = cmd.Execute
 
    Set Me.Form.Recordset = rs

[ADezii]

Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question:

I have a stored procedure that returns a set of records from a SQL Server and loads it into a form in my Access application. This works as intended, but I'm having trouble trying to figure out how to modify records on the form... whenever I try i get the error that says youc an't modify this data because its based on an expression.

i'm just learning stored procedures in access so i was wondering if anybody could help. the code that populates the form is below.

Basically I'm wondering if I can have the application automatically update data without having to create a 2nd procedure to push changes back to the server.

Set gcn = Nothing
    Dim sConnect As String
    sConnect = "Driver={SQL Server};Server=XXXX;Database=XXXXX;Trusted_Connect  ion=Yes;"
 
    Set gcn = New ADODB.Connection
    gcn.CursorLocation = adUseClient
    gcn.Open sConnect
 
    'On Error GoTo ExitProcedure
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    'Call doConnect
 
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
 
    With cmd
        .CommandText = "my_procedure"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@parm1", adChar, adParamInput, 15, Me.cbxData_Set)
        Set .ActiveConnection = gcn
    End With
 
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseServer
    rs.Open cmd, , adOpenStatic, adLockPessimistic
 
    Set rs = cmd.Execute
 
    Set Me.Form.Recordset = rs

Can you post the contents of "my_procedure"?

[binky]

The stored procedure is below. It's a simple SQL query that uses parameters passed from the application.

IF OBJECT_ID('my_procedure') IS NOT NULL
    DROP PROCEDURE my_procedure
GO
 
CREATE PROCEDURE dbo.my_procedure
   (@p_cPrcsNm   CHAR    (15)     
   ,@p_cErrMsg   CHAR    (75)      
   ,@p_vButton   int     
   ,@p_vAssgnCd  VARCHAR (3)       
   ,@p_vRouteCd  VARCHAR (4)       
   ,@p_vStatusCd CHAR    (10)     
   )
AS
 
BEGIN
   SELECT a.*, b.* FROM dbo.cs_br AS a
   LEFT JOIN dbo.cs_tr AS b
   ON a.cv_id = b.cv_id
   WHERE a.prcs_nm = @p_cPrcsNm
   and (@p_cErrMsg IS NULL OR a.err_msg = @p_cErrMsg)
   and (@p_vStatusCd IS NULL OR a.status_cd = @p_vStatusCd)
   and (@p_vAssgnCd IS NULL OR LTRIM(RTRIM (b.assgn_cd)) = @p_vAssgnCd)
   and (@p_vRouteCd IS NULL OR b.route_cd = @p_vRouteCD)
END
 
GO

[binky]

/bump for anybody that might be able to help :)

[ADezii]

/bump for anybody that might be able to help :)

Hello binky, I am by no means an expert on Stored Procedures, but here are a few of my initial observations. I hope that they will point you in the right direction:

1. The Recordset that is dynamically assigned to your Form was opened via a Static Cursor Type, try a Keyset Cursor instead as in:
   

   rs.Open cmd, , adOpenKeyset, adLockPessimistic

2. my_procedure accepts 6 Input Parameters, but I see only 1 being appended to the Command Objects Parameters Collection:
   

   .Parameters.Append .CreateParameter("@parm1", adChar, adParamInput, 15, Me.cbxData_Set)

3. You stated that Input Parameters are being passed from the Application, but how?
4. The SQL Statement as defined in your Stored Procedure, involves a Left Outer Join. Perhaps this could be the reason why the data is not Updateable? Try a Standard Inner Join and see if the results are the same.
5. Fields based on Expressions are, in fact, not Updateable but I see no indication of this.
6. Fields on Forms based on a 1 to Many Join from a Stored Procedure are Updateable if they are in the most-many Table and this Table has been specified as the Unique Table for the Form. Is this your case?
7. You are using Integrated Security within your Connection String (Trusted_Connection= Yes). Do you have the required Permissions on the Server?
8. When you use the Execute() Method of a Command Object with Parameters, the resulting Recordset is Read Only. Try replacing these 2 lines:
   

   rs.Open cmd, , adOpenStatic, adLockPessimistic
   Set rst = cmd.Execute

   with this single line:
   

   rs.Open cmd, , adOpenKeyset, adLockOptimistic, adCmdStoredProcedure

9. As far as I know, you shouldn't need a 2nd Procedure to 'push' data back to the Server.
10. Try each point in turn and see what happens. Keep me informed as to your progress, since you peaked my interest with this one.

[binky]

Thanks for the reply :)

I might have found a workaround, and that's by migrating everything from a MDB project into an ADP project. Then I can use my existing procedure as the recordsource on a form and Access apparently handles the edits with all the appropriate record locks automatically.

But I will also try your suggestions, and see what works best :)

[ADezii]

Thanks for the reply :)

I might have found a workaround, and that's by migrating everything from a MDB project into an ADP project. Then I can use my existing procedure as the recordsource on a form and Access apparently handles the edits with all the appropriate record locks automatically.

But I will also try your suggestions, and see what works best :)

Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question:

Sorry binky, but I was under the impression that you were using an Access Project.