UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Relationships - Sys Tables - Did I Commit An Oopsie?, Access 2016    
 
   
MercenaryX
post Apr 3 2019, 12:08 PM
Post#1



Posts: 4
Joined: 3-April 19



Ok so I manage a large database filled with financial information for several years. I've been doing a pretty decent job.

About a year and a half ago, I rolled out a front end update to the users that made it easier for me to manage queries. It made it possible for me to change user name status information on the fly, and have that information reflected in various queries. Like if a record was assigned to John Doe, and John Doe was part of group 1, all I had to do was make sure John was set to Group 1 in the user name table. Then any query ran for group 1 records would first look at the user table, then find all the records in the main table that apply to users from group 1. This was a great update, because it meant I no longer had to manually edit the queries themselves any time an employee changed groups.

Soon after, it was brought to my attention that while the group number queries worked, editing the records from within those query results was now impossible. The removed feature was not required, but would be nice to have back.

I did a little research and found out that I could restore this feature by creating a table relationship between the user table and main table.

I did, and all worked great.

Fast forward to about two weeks ago. The database was moving much slower now, and I had a hunch it had something to do with the relationship I created. Upon testing it, my suspicions were confirmed. I decided that the poor database performance wasn't worth the unnecessary feature. So I opened the relationships screen, and removed all relationships...

Thats just it. I removed all of them... and yet I could still edit the records in those queries. I have no idea what happened.

I compared the (now devoid of any) relationship screen of my back end to an older version of our back end... and I saw something that made the blood drain from my face. Our current back end was lacking what appears to be some kind of default system table relationship. I must have removed it.

The relationship was between MSysNavPaneGroupCategories, MSysNavPaneGroups, and MSysNavPaneGroup ToObjects.

While I have a strong suspicion this is just something to do with the object organization in the left pane, and nothing serious, I need someone here to tell me if removing this relationship was a mistake that I will soon regret.

Go to the top of the page
 
isladogs
post Apr 3 2019, 02:55 PM
Post#2


UtterAccess VIP
Posts: 1,417
Joined: 4-June 18
From: Somerset, UK


If you aren't using custom groups in the nav pane I doubt you would have noticed a difference.
However I would recommend you do restore those relationships then right click and hide the 3 tables to prevent future accidents.
To my mind its an MS design flaw that system table relationships are shown in the window.

Have a look at this article on my website Relationships and Referential Integrity
This post has been edited by isladogs: Apr 3 2019, 02:57 PM

--------------------
Go to the top of the page
 
MercenaryX
post Apr 3 2019, 03:20 PM
Post#3



Posts: 4
Joined: 3-April 19



and if I don't care about custom groupings in the back end? What then? Should I still restore the relationships? (p.s. the tables are hidden. *shrug*)
Go to the top of the page
 
isladogs
post Apr 3 2019, 03:29 PM
Post#4


UtterAccess VIP
Posts: 1,417
Joined: 4-June 18
From: Somerset, UK


Not sure I understand your reply.
The FE doesn't show relationships in the BE.
I would still suggest you restore the default relationships for those system tables from whichever dB you removed it from.
With system tables, the rule is don't touch unless you know what you are doing.
The real reason for having relationships is for imposing referential integrity. They aren't just like query joins

--------------------
Go to the top of the page
 
projecttoday
post Apr 3 2019, 07:27 PM
Post#5


UtterAccess VIP
Posts: 10,758
Joined: 10-February 04
From: South Charleston, WV


Mercenary, do you know for a fact that you deleted the relationships between MSysNavPaneGroupCategories, MSysNavPaneGroups, and MSysNavPaneGroup?

--------------------
Robert Crouser
Go to the top of the page
 
MercenaryX
post Apr 4 2019, 11:11 AM
Post#6



Posts: 4
Joined: 3-April 19



Yes, I deleted the relationship between the tables I mentioned. For sure.
But as long as it doesn't cause any system instability, or consequences that are gonna make me have a bad time, I don't see any need to restore them.

I was referring to the relationship specifically in the back end because thats where I deleted it from. I don't have any object organization needs in the back end. I do have custom groups in the front end and I never touched those, and they appear to still be in tact as you would expect.

Go to the top of the page
 
isladogs
post Apr 4 2019, 12:40 PM
Post#7


UtterAccess VIP
Posts: 1,417
Joined: 4-June 18
From: Somerset, UK


You were worried you might have made a mistake.
Have you examined the contents of those three system tables? Do you understand the purpose of each?
Do you understand why they were linked using RI?
Are you SURE you can do without that in your BE?

Unless you are ABSOLUTELY CERTAIN you know they aren't needed I suggest you restore them.
If RI can be reinstated no harm has occurred so far.
If not, then you have indeed made a mistake you may regret

--------------------
Go to the top of the page
 
MercenaryX
post Apr 5 2019, 11:17 AM
Post#8



Posts: 4
Joined: 3-April 19



Thank you for the advice. Since I'm not really all that certain what the tables do precisely or what their contents mean, I'll re-link them in a way that was present before I made the change.
Go to the top of the page
 
cheekybuddha
post Apr 5 2019, 11:20 AM
Post#9


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Out of curiosity, what did you hope to achieve by removing all table relationships?

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Apr 5 2019, 11:26 AM
Post#10


Access Wiki and Forums Moderator
Posts: 75,559
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
Thank you for the advice. Since I'm not really all that certain what the tables do precisely or what their contents mean, I'll re-link them in a way that was present before I made the change.
I was thinking it might be safer to create a new blank database file and just import all your tables into it. Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 06:46 PM