Multiple backends and relationships

[matt beamish]

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

 

[theDBguy]

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.

 

[matt beamish]

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]

And I should have included that jobno and taskrecid are combined as a unique index in t-jobtasks.

 

[Cronk]

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

Another method would be to put the relationships in the front end.

 

[theDBguy]

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.

 

[NauticalGent]

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...