By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,077 Members | 2,160 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,077 IT Pros & Developers. It's quick & easy.

Updating Data in a Recordset (Stored Procedure)

P: 19
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.

Expand|Select|Wrap|Line Numbers
  1. Set gcn = Nothing
  2.     Dim sConnect As String
  3.     sConnect = "Driver={SQL Server};Server=XXXX;Database=XXXXX;Trusted_Connect  ion=Yes;"
  4.  
  5.     Set gcn = New ADODB.Connection
  6.     gcn.CursorLocation = adUseClient
  7.     gcn.Open sConnect
  8.  
  9.     'On Error GoTo ExitProcedure
  10.     Dim rs As ADODB.Recordset
  11.     Set rs = New ADODB.Recordset
  12.     'Call doConnect
  13.  
  14.     Dim cmd As ADODB.Command
  15.     Set cmd = New ADODB.Command
  16.  
  17.     With cmd
  18.         .CommandText = "my_procedure"
  19.         .CommandType = adCmdStoredProc
  20.         .Parameters.Append .CreateParameter("@parm1", adChar, adParamInput, 15, Me.cbxData_Set)
  21.         Set .ActiveConnection = gcn
  22.     End With
  23.  
  24.     Set rs = New ADODB.Recordset
  25.     rs.CursorLocation = adUseServer
  26.     rs.Open cmd, , adOpenStatic, adLockPessimistic
  27.  
  28.     Set rs = cmd.Execute
  29.  
  30.     Set Me.Form.Recordset = rs
Nov 2 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,627
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.

Expand|Select|Wrap|Line Numbers
  1. Set gcn = Nothing
  2.     Dim sConnect As String
  3.     sConnect = "Driver={SQL Server};Server=XXXX;Database=XXXXX;Trusted_Connect  ion=Yes;"
  4.  
  5.     Set gcn = New ADODB.Connection
  6.     gcn.CursorLocation = adUseClient
  7.     gcn.Open sConnect
  8.  
  9.     'On Error GoTo ExitProcedure
  10.     Dim rs As ADODB.Recordset
  11.     Set rs = New ADODB.Recordset
  12.     'Call doConnect
  13.  
  14.     Dim cmd As ADODB.Command
  15.     Set cmd = New ADODB.Command
  16.  
  17.     With cmd
  18.         .CommandText = "my_procedure"
  19.         .CommandType = adCmdStoredProc
  20.         .Parameters.Append .CreateParameter("@parm1", adChar, adParamInput, 15, Me.cbxData_Set)
  21.         Set .ActiveConnection = gcn
  22.     End With
  23.  
  24.     Set rs = New ADODB.Recordset
  25.     rs.CursorLocation = adUseServer
  26.     rs.Open cmd, , adOpenStatic, adLockPessimistic
  27.  
  28.     Set rs = cmd.Execute
  29.  
  30.     Set Me.Form.Recordset = rs
Can you post the contents of "my_procedure"?
Nov 2 '07 #2

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

Expand|Select|Wrap|Line Numbers
  1. IF OBJECT_ID('my_procedure') IS NOT NULL
  2.     DROP PROCEDURE my_procedure
  3. GO
  4.  
  5. CREATE PROCEDURE dbo.my_procedure
  6.    (@p_cPrcsNm   CHAR    (15)     
  7.    ,@p_cErrMsg   CHAR    (75)      
  8.    ,@p_vButton   int     
  9.    ,@p_vAssgnCd  VARCHAR (3)       
  10.    ,@p_vRouteCd  VARCHAR (4)       
  11.    ,@p_vStatusCd CHAR    (10)     
  12.    )
  13. AS
  14.  
  15. BEGIN
  16.    SELECT a.*, b.* FROM dbo.cs_br AS a
  17.    LEFT JOIN dbo.cs_tr AS b
  18.    ON a.cv_id = b.cv_id
  19.    WHERE a.prcs_nm = @p_cPrcsNm
  20.    and (@p_cErrMsg IS NULL OR a.err_msg = @p_cErrMsg)
  21.    and (@p_vStatusCd IS NULL OR a.status_cd = @p_vStatusCd)
  22.    and (@p_vAssgnCd IS NULL OR LTRIM(RTRIM (b.assgn_cd)) = @p_vAssgnCd)
  23.    and (@p_vRouteCd IS NULL OR b.route_cd = @p_vRouteCD)
  24. END
  25.  
  26. GO
Nov 5 '07 #3

P: 19
/bump for anybody that might be able to help :)
Nov 5 '07 #4

ADezii
Expert 5K+
P: 8,627
/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:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. .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:
    Expand|Select|Wrap|Line Numbers
    1. rs.Open cmd, , adOpenStatic, adLockPessimistic
    2. Set rst = cmd.Execute
    with this single line:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Nov 6 '07 #5

P: 19
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 :)
Nov 7 '07 #6

ADezii
Expert 5K+
P: 8,627
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.
Nov 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.