RELATIONSHIPS IN FRONT END FILE 3

[PPJOSEPH]

Hi:
I created a db with 6 tables. I did not create any relationships. Instead, I did the individual relationship in each query. I split the db as the front end and back end files. When I did a performance analyzer on the front end file, it suggested creating relationships. I clicked OK and it created relationship but in the front end file.
Now my question is - Is it okay to leave the relationship in the front end file or should I delete what the performance analyzer did and create the relationships in the backend file?

 

[grande]

The relationships should be where the queries are. It makes them run faster.

-------------------------
Just call me Captain Awesome.

 

[BNPMike]

The relationships should be where the queries are. It makes them run faster.

I'm interested - what make you say that?

 

[ZmrAbdulla]

One reason I can say that..
BE will be one and FE many. So if you want to change any relation you will change at the BE and it will affect to all.

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PPJOSEPH]

Zameer, if I understood it right, Grande recommends to leave the relationship in the front end file.
Couple of points that I forgot to mention in my question are:
1. There are almost 100 users spread around the country.
2. The FE file has been pushout to the users desktop.

If don't know if the above would make any difference.
Paul

 

[ZmrAbdulla]

So you have to push the FE everytime you make changes.
Let Grande's reply come.

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[grande]

Yeah, I didn't realise the scope of your project... BE it is!

-------------------------
Just call me Captain Awesome.

 

[PPJOSEPH]

No, Zameer. I have an auto update function in the FE file that is controlled by a version number. When the user double clicks on the icon that is on his desktop, the desktop version number is verified against the one that is on the server and if different will start a seemless copying. The user will get a message at the end of the copying that his copy has been updated to the latest version on the server. It works great. The only bug in it is that while copying which might take up to 30 seconds, it gives a blank MS Access screen. Now thinking of that may be I should have a message splash warning that copying is in progress. May be another thread.

Awesome Captain thanks for your clarification
BNPMike thanks for taking up on the question and
Zameer for restating that the relationship should be in the back end file.
You three deserve a star each for participating in the discussion.
Thanks
Paul

 

[willir]

The relationships should be where the queries are. It makes them run faster

Aside from running faster, how does Access implement relationship rules if the FE from one user is correct, and FE either does not have the relationships defineed or defined differently?

I set up a test BE with two different FE.

What I learned...
You can NOT force referential integrity when defining relationships from the FE. And if you take advantage of the features, you can not cascade updates and deletes.

You can inadvertantly define a relationship wrong with one of the FE's.

I would opt for data integrity over speed anytime.

The additional administration issue has been addressed although database schemas are not often changed.

grande - Do you have a reference on the speed thing when defining relationships? I would be inteyrested in following up on my education.

Thanks

Richard

 

[ZmrAbdulla]

There are almost 100 users spread around the country.

Just curious.. How the users connect to the BE DB? Intranet/Extranet?
Thanks for the

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PPJOSEPH]

Zameer:
The db is placed on a citrix server and everyone is mapped to it. In fact my next project is to load it on the intranet. But at this point, its working smoothly.

To Willir's question: When I started working on the db, I did the relationship on individual queries and as time went on I stuck with it. Lately I read an article that if you do the relationship with the tables its more quicker. It also said that the relationship should be done in the BE file. In my instance, everyone has the same FE file.

 

[willir]

The Citrix server is a great solution! I think it is far better solution than using replication.

Next, with a Citrix server, they should be using the FE on the Citrix server -- this minmizes the network traffic which kills performance. Data only has to travel from the BE to the FE which could be on the same server, or at least on the local LAN. (I wonder if you are using one FE which is located on the Citrix server ... or does each user have their own front end loaded in their HOME directory on the Citrix server)

This way, the only thing transmitted across the WAN / Internet / DSL is the screen updates and keyboard controls.

I say again, if it is not too much trouble, I would be really interested in reading the article you refer to -- always trying to learn more.

Thanks
Richard