Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 04:17 AM   #1
KEKeogh
Newly Registered User
 
Join Date: May 2011
Location: Philadelphia Area
Posts: 80
Thanks: 4
Thanked 1 Time in 1 Post
KEKeogh is on a distinguished road
Lock a specific table Access 2013

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.

KEKeogh is offline   Reply With Quote
Old 08-15-2019, 04:24 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,822
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Lock a specific table Access 2013

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-15-2019, 05:39 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lock a specific table Access 2013

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-15-2019, 07:25 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,631
Thanks: 38
Thanked 885 Times in 868 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Lock a specific table Access 2013

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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-15-2019, 09:15 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lock a specific table Access 2013

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-15-2019, 11:29 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,822
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Lock a specific table Access 2013

Quote:
Originally Posted by Pat Hartman View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-15-2019, 02:00 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lock a specific table Access 2013

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-16-2019, 05:29 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,822
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Lock a specific table Access 2013

Hi Pat
My response was aimed at the 'general audience' for clarification rather than at yourself or the DBGuy
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-16-2019, 04:41 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lock a specific table Access 2013

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import info from Word 2013 form into Access 2013 via VBA oddstan Modules & VBA 8 12-03-2017 05:06 AM
Lock a table in MS Access AnitaRajam General 3 08-29-2014 03:49 AM
Question Package 2013 Access db with 2013 runtime, standalone application? Nina09 General 0 01-02-2014 01:09 AM
LOCK TABLE in Access db MsLady General 3 11-20-2007 06:54 PM
Lock Specific Records In Table thinkhard Tables 7 07-20-2007 04:13 AM




All times are GMT -8. The time now is 12:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World