Lock a specific table Access 2013 (1 Viewer)

KEKeogh

Registered User.
Local time
Today, 10:01
Joined
May 4, 2011
Messages
80
My apologies if this has been asked before.

I have a table with a list of our reps for the company but for some reason over the course of working in other forms all the reps but one will delete.

Not sure what form is causing this to happen but I'm thinking if I can lock the reps table then maybe I can stop it from happening anymore.

Thanks for any help.
 

isladogs

MVP / VIP
Local time
Today, 14:01
Joined
Jan 14, 2017
Messages
18,186
Do you want to prevent users deleting records or delete the one you can't at the moment?

End users should only interact with data using forms.
They should never have any access to tables or queries.
Forms can be locked partly or completely to prevent deletion or editing as you wish.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 19, 2002
Messages
42,970
For now, make sure you have a good backup but you really need to find the form. Look for a form that has a search feature. If it is using a bound control, the search box may be overwriting existing records.

Access no longer has user level security (ULS) so you can't do what you are asking in any direct way unless the BE is SQL Server. But of course locking the table in SQL Server locks it for everyone unless you create separate security groups.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:01
Joined
Oct 29, 2018
Messages
21,357
Hi. Do you have referential integrity enforced on the reps table? If you're trying to delete all reps records and one won't, maybe there is a child record associated with it, and you'll have to delete it first before you can delete the rep record. Just a thought...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 19, 2002
Messages
42,970
theDBGuy has a point.

If you enforce Referential Integrity (as you should), the user will not be able to delete a rep if he has a relationship with some other table.
 

isladogs

MVP / VIP
Local time
Today, 14:01
Joined
Jan 14, 2017
Messages
18,186
theDBGuy has a point.

If you enforce Referential Integrity (as you should), the user will not be able to delete a rep if he has a relationship with some other table.

Not necessarily.
If RI is applied along with Cascade Delete you will be able to delete the record and the related record(s) in the child table will also be deleted. Access will warn you first of the consequences.
See Relationships and Referential Integrity
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 19, 2002
Messages
42,970
Well, I did assume that knowing when it was appropriate to specify Cascade Delete and when it wasn't would be part of the solution. Perhaps that was too obvious to pick up from my suggestion.

If you don't want to delete reps that are used elsewhere, you would NOT specify cascade delete on the relationship.
 

isladogs

MVP / VIP
Local time
Today, 14:01
Joined
Jan 14, 2017
Messages
18,186
Hi Pat
My response was aimed at the 'general audience' for clarification rather than at yourself or the DBGuy ;)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 19, 2002
Messages
42,970
No problem. It's better to be completely clear. Cascade delete is very powerful and I use it for some relationships in every application I build. It is just not appropriate for all relationships.
 

Users who are viewing this thread

Top Bottom