Why is SQL Server recordset read only / non-updatable

[RobbieBee]

Hi,

I'm using VBA in Access 2010 to open a DAO recordset and populate with results from a SQL Server 2008 R2 stored procedure. However, when I try to edit the contents with the '.edit' method I get error 3027 ( Cannot update. Database or object is read-only). Can anyone explain why this is happening and what the solution is? I'm using the following code:-

SQL Server 2008 R2 stored procedure:

--Parameters
 
@User_ID        AS    INT
,@Dayfile_Status    AS    INT
 
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT             Work.*
 
    FROM             Work
 
    WHERE            (Date_Dayfiled_For                    <=    GetDate()
        Or                                    Date_Dayfiled_For Is Null)
    AND                                        Date_Completed    Is Null
    AND            User_ID_FK =    @User_ID
    AND            Help_Needed =    0
    AND                                        Dayfile_Status_ID_FK                =    @Dayfile_Status
 
    ORDER BY        IsNull(Date_Dayfiled_For,N'2099/12/31')
                ,MPAN_CORE
 
END
 
 
--Access 2010 VBA test script
 
Dim qdfMain As DAO.QueryDef
Dim rsMain As DAO.Recordset
 
 
Set qdfMain = CurrentDb.CreateQueryDef("")
 
With qdfMain
    .Connect = CnStr
    .SQL = "EXEC WF.Dayfile_Due " & _
            "@User_ID=1" & _
            ", @Dayfile_Status=1"
    .ReturnsRecords = True
    Set rsMain = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
End With
 
rsMain.Edit
rsMain("User_ID") = 2
rsMain.Update
 

I have update permissions on the table and database in SQL server and the connection string logs me in using 'trusted connection=Yes'. The stored procedure returns the appropriate records which contain records from only one table and that table has a primary key and also a timestamp field so I'm unclear why this would be seen as a read only recordset.

Any help would be greatly appreciated!

Thanks

[Seth Schrock]

Does your table have a primary key that Access knows about?

[jimatqsi]

You can execute a stored procedure from Access but I don't believe you can retrieve an updateable recordset from a stored procedure. Stored procedures produce calculated results. It would be like trying to update a recordset that had a column for Sum() or Avg() ... it doesn't make sense.

You could do what you want several ways. One is to simply create a similar stored procedure that accepts parameters and does the update you want to do. Another would be to open an ODBC to the Work table and open the table with the appropriate where criteria. You can also make a pass-through query to do the same thing; the SQL of the Pass-Through query could be modified dynamically as needed.

Not knowing your situation, I can't say which of those, if any, would be the best approach. But I'm pretty sure what you are trying to is not possible.

Jim

[RobbieBee]

Seth: I had assumed when opening the recordset via VBA code that SQL server would have highlighted the primary key field in the returned recordset so Access would work know and work with it but it's the first time I've connected to SQL Server like this so I may be wrong.

Do you know if there is some code that I should use to highlight the primary key field to Access?

Thanks

[Seth Schrock]

I don't know any code that can do this for a stored procedure. However, if you would create a linked table within Access, Access usually recognizes it (as long as you link to the table and not a view). If it doesn't I do know the code to add it.

[RobbieBee]

Ah Ok, thanks. I'm trying to avoid using linked tables because the queries I will be running are likely to be too complex for Access to send through to SQL Server in one single translated query. I've read that this will result in Access sending the query to SQL Server in several parts and the net result is that SQL Server ends up sending most of the data to the Access front end to do the processing, which defeats my aim of putting the data on the server to avoid network traffic and enable the server to do all the processing.

[Seth Schrock]

So then you should create a third parameter that stores the new user id and then have the stored proc be a update query instead of a select query. Then all the action is taking place on the server and Access has nothing to do with it other than passing the arguments and telling it to run.

[RobbieBee]

Yes, that makes sense. I think it would be helpful for me to clarify that the way the database is intended to work is as follows:-

1) Select a set of records (New Work, Day file work or Individual item search etc)for the user to work on from the larger set.
2) Feed them one record at a time on an unbound Access form and if they are working on new work then allocate that record to them upon displaying it.
3) Save any changes the user has made to the record when they move from one record to another.
4) Display the total records in their recordset and have a counter of how many records they have worked through so far.

I have already built some tidy code to do this using a DAO recordset/Access back end with linked tables, using the .edit and .update methods to save the changes (in Step 3) so was hoping to just use that same code with the SQL Server recordset, so all record manipulations are done via recordset. I hope that makes a bit more sense now. If I am unable to use the .edit method do you know if running the separate update stored procedure mentioned would automatically and immediately replicate the changes back through to the read only recordset the user is working through in Access (and any other users that have that record present in their recordsets)?

[Seth Schrock]

No matter how you are getting the data, using the .Edit method is performing the job on the local PC, not on the server. So this is totally defeating the goal of having everything done on the server while at the same time adding lots of overhead to your program. If you want it to be totally on your server, then you would need to build an UPDATE query as a passthrough query.