Accdb linked view error: "Cannot update. Database or object is read-only"

This regards an Access 2010 accdb with a SQL Server back end: This front end is using a linked view to enter or update data (using INSTEAD OF trigger). The problem is that every so often it gives the "Cannot update. Database or object is read-only" error when trying to enter or update data through this view. The only thing that fixes this issue is deleting the linked view, relinking it and setting the primary key again. However, this fix doesn't last, the problem usually shows up again the next day and has to be fixed again.
Is there a way to do this automatically? That is when the user opens the front end some code runs that relinks this view and sets its primary key? Any other ideas on how to solve this issue?

Thanks!
Answer
Answer

What we do at my workplace is use a tool to index the views.  We have a table with these fields in our tool:

 

tblViews

IndexName - Text(255)

ViewName - Text(255)

KeyField - Text (255)

 

And then we use this code to set the indexes whenever we need to relink (which is only when we move from Test to QA and then from QA to Production.

 

Option Compare Database
Option Explicit

Public Sub SetPrimaryKeys(strDBName As String)

On Error GoTo Errors

Dim CurrDB As DAO.Database
Dim RemoteDB As DAO.Database
Dim rstTables As DAO.Recordset
Dim tdf As DAO.TableDef
Dim strSQL As String
Dim strMissingTables As String
Dim strTableNames() As String
Dim intItem As Integer

Set CurrDB = CurrentDb
Set RemoteDB = DBEngine.OpenDatabase(strDBName)
Set rstTables = CurrDB.OpenRecordset("tblViews")

'Load an array with the name of each Barkley table.
ReDim strTableNames(RemoteDB.TableDefs.Count, 1)
For Each tdf In RemoteDB.TableDefs
    If tdf.Attributes = dbAttachedODBC And Mid(tdf.Name, 6, 3) = "sel" Then
        strTableNames(intItem, 0) = tdf.Name
        intItem = intItem + 1
    End If
Next tdf

With rstTables
    .MoveFirst
    Do Until .EOF
        'Mark the table in the array so we know it's in our master views table
        For intItem = 0 To UBound(strTableNames) - 1
            If "dbo_" & !viewname = strTableNames(intItem, 0) Then
                strTableNames(intItem, 1) = "1"
                Exit For
            End If
        Next intItem
        'Create the index
        strSQL = "CREATE UNIQUE INDEX " & !indexname & " ON dbo_" & !viewname & " (" & !keyfield & ") WITH Disallow Null"
        RemoteDB.Execute strSQL
        .MoveNext
    Loop
End With

'Check the array for missed tables
For intItem = 0 To UBound(strTableNames) - 1
    If strTableNames(intItem, 0) = "" Then
        Exit For
    Else
        If strTableNames(intItem, 1) <> "1" Then
            strMissingTables = strMissingTables & strTableNames(intItem, 0) & vbCrLf
        End If
    End If
Next intItem

If Len(strMissingTables) > 0 Then
    MsgBox "The following views are not in the master view table:" & vbCrLf & vbCrLf & strMissingTables, vbInformation, "Missing Views"
End If

Beep
MsgBox "The views in " & strDBName & " have been successfully indexed."

ExitHere:
    If Not rstTables Is Nothing Then rstTables.Close
    Set rstTables = Nothing
    Set RemoteDB = Nothing
    Set tdf = Nothing
    Exit Sub
   
Errors:
    Select Case Err.Number
        Case 3283
            'Primary Key already exists
            Resume Next
        Case 3371
            'Table doesn't exist
            Resume Next
        Case Else
             MsgBox "Unexpected Event " & Err.Number & " - " & Err.Description
    End Select

Resume ExitHere

End Sub

 

 

 

The entry in the table looks like this:

 


IndexName    ViewName           KeyField
cpa_id             dselAccountants   cpa_id

 

-----
Microsoft Access MVP 2008, 2009, 2011
If a post was helpful click the FOUND THIS HELPFUL link

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated December 19, 2019 Views 474 Applies to: