record lock/read only

[Malcolmneill]

I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table. So Far a solution eludes me!!

 

[CJ_London]

short answer is no. However the question implies that your db design could be improved.

You can protect it by not allowing users direct access to the table, and have some code in a form before delete event to abort the delete, or always open the table as a snapshot - but then you won't be able to change other records.

If it is fixed data, never to be changed, consider hardcoding it or storing as a db property

 

[gerrythefish]

I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table. So Far a solution eludes me!!

Short answer is not that Im aware of.

If you can always use a form to read/edit the data (either form, continuous form or datasheet will work) then you can add a procedure to run on the Current Record event as shown.

Private Sub Form_Current()
    On Error GoTo ErrorHandler
     
         
    If Me.KeyField_ID.Value > 1 Then
        'KeyField_ID is the primary key field in the table 
        'or other of known value
        'User has access
        Me.lblReadOnly.Visible = False
        'I show a label with text "Read Only" at the top of the form if Read only -
        'form view only - not datasheet
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
    Else
        Me.lblReadOnly.Visible = True
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowEdits = False
        
    End If
Exit_OnError:
    Exit Sub
    
ErrorHandler:
    ErrorAlertUser err, Me.Name, "Form_Current"
End Sub

 

[jdraw]

I tend to agree with the others. However, if you really want to protect that 1 record(hopefully with some sort of unique identifer, you could try the folllowing (totally untested).
If the users can ONLY access data via your front end:

Create 2 recordsets:

1) Only the record you want, Open that recordset with dbReadOnly
eg db.Openrecordset ("Select * from yourTable where recordid = 001",,dbReadOnly)
that should make it non-updateable via the database software


2)All other records, Open that recordset with dbOpenDynaset
eg.db.Openrecordset ("Select * from yourTable where recordid <> 001",,dbOPenDynaset)


All untested. But there may be better ways to accomplish what you want/need.

 

[essaytee]

I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table. So Far a solution eludes me!!

I agree with comments from others thus far but I am curious as to why the first record needs to be locked. Knowing the reason should open up this thread for more suggestions.

 

[Malcolmneill]

Hi all,

Thanks for the input, all useful. Yes the DB design could be better, but it's a bit late to change it unfortunately as it would require a whole new working methodology for the users which is not practical. Maybe a future system design.

I think the method of using the suggested code is probably the best option for now, which is what I was thinking but wondered what others might suggest.

As to the "Why", that's a long story and probably shows my inexperience at the time of developing the application. However, I used the record as a control record at the start of that particular Table, which is great until something goes wrong and it gets corrupted!!! I can "control it" via the form(s) that use the table so I can code that OK and I like the idea of maybe having a separate dataset as a future design.

So thanks for all the replies
Malcolm