Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2015

    Unanswered: Why is this SQL Server recordset read only / non-updatable?


    I'm using VBA in Access 2010 to create a connection to a SQL Server database and 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:


    @User_ID AS INT
    ,@Dayfile_Status AS INT


    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')


    --Access 2010 VBA test script

    Public Const cnStr = "ODBC;Description=DB1;DRIVER=SQL Server;SERVER=SERVER01;Trusted_Connection=Yes;DATA BASE=DB1"

    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("Dayfile_Status_ID_FK") = 2

    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 managed as a read only recordset by Access. I'm wondering if I have to code something to inform Access which field is a primary key or if that is automatically highlighted when SQL Server returns the recordset?

    NOTE: I am not using linked tables as the nature of my queries means Access would be unable to translate them as 1 query for SQL Server so they would be split resulting in multiple server requests and SQL Server sending most of the data back to Access to process with the accompanying heavy network traffic and slow processing.

    Any help would be greatly appreciated!


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    At least as far as I know, a DAO pass-through query is always read-only.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2015
    Oh, that would explain my problem then, thanks!

  4. #4
    Join Date
    Apr 2004
    I have you ever considered using Jet Link DAO instead? ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts