Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-21-2019, 12:00 PM   #1
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 173
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Multiple backends and relationships

Hi folks.
I have three backends with multiple front ends which have all been playing nicely with each other for well over 10 years now and regularly have 40 users over a network. Relationships are set in each back end and the structure has been fine.
However a change in a table structure and addition of a new PK indexed field means that I need to set a relationship and maintain Ref Integrity across backends, except that can't be done, so I need another solution.

One method would be to consolidate the backends into one, but that seems quite a hefty hammer for this particular nut.

Can anyone give me any help on how to navigate through this? Perhaps I need to check data with a beforeupdate event but I need help knowing what the queries behind this are going to look like.

thanks in advance
Matt

matt beamish is offline   Reply With Quote
Old 06-21-2019, 12:09 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Multiple backends and relationships

Hi Matt. Where is the backend data stored? Access or SQL Server tables? What is this new PK/FK relationship between BEs? If you're using SQL Server, you might be able to use (although I'm not entirely sure on this) Triggers.
__________________
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.

Last edited by theDBguy; 06-22-2019 at 08:38 AM.
theDBguy is online now   Reply With Quote
Old 06-21-2019, 12:31 PM   #3
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 173
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Multiple backends and relationships

Hi DBGuy.
they are Access tables. IT have never shown any interest in helping me out with SQL server ;-(

I'm showing 3 tables in 2 backends.

Project be

t_project
JobNo-PK


t_projecttasks
TaskRecID-PK
JobNo-FK


Timesheet be

t_dailyjobs
JobRecID-PK
TaskRecID-FK
JobNo-FK

JobNo was the simple key, which in primary in t_Project and foreign in t_dailyjobs and now also in the newly introduced table "t_projecttasks". What bothers me is that I am now going to be repeating the combination of "JobNo and "TaskRecID" in T_dailyjobs and if the structure isn't right, I am going to create an inconsistency with t_Projecttasks. I need a routine to check that the combination of "JobNo and TaskRecID" in t_dailyjobs is consistent with what exists in t_projecttasks beforeupdate.

In truth, the whole should be redesigned to remove "JobNo" from "t_dailyjobs" but this is a huge job with loads of ****up potential.

thanks for interest.

matt beamish is offline   Reply With Quote
Old 06-21-2019, 01:38 PM   #4
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 173
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Multiple backends and relationships

And I should have included that jobno and taskrecid are combined as a unique index in t-jobtasks.
matt beamish is offline   Reply With Quote
Old 06-21-2019, 05:30 PM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,125
Thanks: 3
Thanked 462 Times in 455 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Multiple backends and relationships

Quote:
One method would be to consolidate the backends into one....

Another method would be to put the relationships in the front end.
Cronk is offline   Reply With Quote
Old 06-22-2019, 07:53 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Multiple backends and relationships

Quote:
Originally Posted by Cronk View Post
Another method would be to put the relationships in the front end.
But the OP said he needs/wants to maintain Referential Integrity too. Maybe he's going to have to roll his own RI feature.
__________________
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 online now   Reply With Quote
Old 06-22-2019, 08:08 AM   #7
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,870
Thanks: 404
Thanked 269 Times in 235 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Multiple backends and relationships

Hi Matt, at the least I would look into combining the backends into one. But the real reason I am responding is to hopefully impress upon you the SQL Server option.

If you have 40+ users then it is probably safe to assume your application is beyond a mere gadget that is “nice to have” and is a viable tool your organization values.

If I have that bit right, then it absolutely imperative that you migrate your BE to SQL Server. Although it may or may not increase performance (for me it did), the risk of corruption is all but eliminated. Additionally, the Maintenance Plans the SQL Server offers such as backups, integrity checks, index rebuilds and others make it a necessity.

It took me about 2 years and a near catastrophe for them to come around, but they did and life has been a slice since then.

As a rule, I do a compact and repair once a month prior to re-deploying the front end to my users and a complete decompile once a year. It could be my imagination but I swear my application started running faster after I started this regimen.

Best of luck...


__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Reply

Tags
multiple backend , referential integrity

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
1 front end, multiple backends for different uses darren_access General 9 01-19-2018 12:36 PM
Multiple Open backends zgray General 2 09-20-2013 09:06 AM
Question Multiple Backends? thechazm General 7 03-22-2011 05:54 PM
Multiple Backends Treason Modules & VBA 1 05-22-2007 08:20 PM
Linking to multiple Backends JACKSON General 4 11-26-2004 04:09 PM




All times are GMT -8. The time now is 08:20 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