Possible to prevent deletion but allow insert and update in Access?

[pharmkitty]

I've googled for an answer to this but can't find whether it's possible. I can do this using SQL Server with a .NET front end but my users want to stay with Access. The application they have is very, very simple with just a form and a table. I'm open to writing some VB code if that will work. I don't have a lots of experience with VB.
Thanks in advance.

[ranman256]

Yes, you can set those properties in the form.

[pharmkitty]

Thanks! Could please point me to a reference or tell me how? Just a hint is fine. Is it something to do with this: "If you want to prevent changes to existing records (make a form read-only), set the AllowAdditions, AllowDeletions, and AllowEdits properties to No. You can also make records read-only by setting the RecordsetType property to Snapshot."? https://msdn.microsoft.com/en-us/vba...roperty-access is where I found it. You saying form gave me a good keyword to find information.

I tried setting "Allow Deletion" from the form property to "No" but didn't seem to make a difference.

[ranman256]

yes set all those props,
and Snapshot will not allow edits.

[pharmkitty]

Thanks again. I added some confusion with my response. I want to allow updates and inserts but not allow deletions. I set AllowDeletions to "No" but I don't see a difference. Does someone delete a record from access by setting all fields to blank and moving to another record? I can still do that with the AllowDeletions set to "No". I realize I am rusty at Access now because I don't know how one deletes a record unless by going to the table, selecting a row and deleting it. is there a way a user can do it via the form?

[CJ_London]

Does someone delete a record from access by setting all fields to blank and moving to another record?

no - that just leaves a record without any data. allow deletions (all the allows) refers to the record, not individual fields.

is there a way a user can do it via the form?

assuming allow deletions is set to yes, then depends on what you have on the form. If you are showing the record selectors (on the right of the form), the user can select one, right click and select cut to remove the record. If the record selector is not visible, the record cannot be deleted from the form - unless you provide a button and some code to delete the record.

[pharmkitty]

no - that just leaves a record without any data. allow deletions (all the allows) refers to the record, not individual fields.
assuming allow deletions is set to yes, then depends on what you have on the form. If you are showing the record selectors (on the right of the form), the user can select one, right click and select cut to remove the record. If the record selector is not visible, the record cannot be deleted from the form - unless you provide a button and some code to delete the record.

Thanks Ajax. It is a very simple form such as one would get if they just made a form based on a table without any code behind it. No buttons. It uses the record navigator at the bottom left that is by default. I don't see a way to delete a record via the form. I tried setting "AllowDeletions" property of the form to "no" and then back to "yes" to see if I could find a difference. I didn't see a difference. The user did not describe the problem other than saying she doesn't want her staff to delete. She is not available to ask if she means deletion by using the table to delete a row. I would propose hiding the tables if she means that. I just wondered if there's a way for a user to delete a record with the form. I can't find it.

[CJ_London]

your terminology is confusing - but to clarify. On a form, if you can see these record selectors, then the user can delete a record if allowdeletions is yes.



setting the allow deletions does not affect whether or not the record selectors are visible - that is controlled by the record selector properties

So you wouldn't see a difference between a form set to allow deletions and one where it is not allowed

She is not available to ask if she means deletion by using the table to delete a row.

not clear what this means a row is a record - a table is not a form, so if a user has direct access to a table, they can delete a record

[pharmkitty]

Thanks and I know my terminology must be confusing. I think it's because I was trying to use Access terminology but got it wrong. I'm used to saying rows for records and column names (columns) for fields but I had the mistaken impression that Access was different. Thanks for all the clarification. Two questions: 1) Say there is a form without any buttons. If I hide the selector how would they navigate through the rows? and 2) How do delete a row using the selector? If I understood the answer to the second question especially it would really help. I'm trying to see what a user could do with the selector to delete a row ON A FORM, it would help me reproduce the problem.

I had been thinking was the record selector was actually navigation. What is the record selector? I don't see that on this form. What does it look like? I hid it in the properties of the form but I never saw it in the first place.
Thanks again.

[CJ_London]

read my posts and understand

I'm used to saying rows for records and column names (columns) for fields but I had the mistaken impression that Access was different

it is - Excel uses rows and columns, access uses records and fields - but some will refer as rows and columns in error

Say there is a form without any buttons. If I hide the selector how would they navigate through the rows?

they use the scroll bar or navigation buttons at the bottom of the form

How do delete a row using the selector?

see posts 6 and 8

What is the record selector?

see post 8

[pharmkitty]

read my posts and understand

it is - Excel uses rows and columns, access uses records and fields - but some will refer as rows and columns in error
they use the scroll bar or navigation buttons at the bottom of the form

I'm used to SQL Server not Access. I still don't know how someone would do a deletion of a record/row using the generic navigation/record selector from a form. The form that someone made for thses people is generic with the deafult navigation/record advance/search bar at the lower left. It's Access 2016 if that makes a difference. I think because I've never seen a "record selector" on the right regardless of whether I toggle thast form property to "no" or "yes", it's not clear to me. The navigation bar in the lower left will appear and hide if I toggle that form property. Also, if I right click on a form record the cut is grayed out. I see where I can hide everything and put buttons on the form to advance and backup through records, go to first, filter, etc. so I can try that.

It's different than .NET/SQL Server, no doubt about that :-)

[CJ_London]

I think because I've never seen a "record selector" on the right

that's my mistake, although post #8 should make it clear - they are on the left