Do relationships belong in Front End or Back End?

[nerdcore]

I have a database that has a Front End with all forms/queries/reports, and a Back End with the Tables. When I used the database splitter, it created the relationships in the back end as well as left the original relationships in the front end. I am now adding two new tables to the Back End which need to have relationships. Do I add this to the Front End or Back End Relationships window? Thanks!

 

[Trendsetter]

The relationships belong to the tables and consequently should be in the backend with all of your tables!

 

[ghalewood]

Hi,

I once used the database splitter to create a FE/BE environment. Within the system, I had a fairly intensive module whose run time went from 4 seconds to 12 seconds due to the split and it using links to the BE. This was unacceptable to the users so I amended all the code in the modules to directly point to the BE
(Forms!main_menu!backend_location being a field on the Main menu, populated on startup, from a table in the FE, this way the BE can be moved about without amending the module code)

Dim myDB As Database
.
.
strDatabaseloc = Forms!main_menu!backend_location
Set myDB = OpenDatabase(strDatabaseloc)

rather than

Dim myDB As Database
.
.
Set myDB = CurrentDb()

This then reduced the run time of my module back to 4 seconds.

Just thought I would let you know that using the DB splitter in Access severely increases run times of queries and update time of screens based on table data.

 

[LittleSmudge]

Following on from the last comment in the previous post about splitting the db Front and Back increasing run times . . ..

Let me explain why and a how to overcome.


Many people do not realise that if you have a split database then the Front End has to establish a link ( open up ) the Back End every time you open a Form or a RecordSet bound to that Back End.

( As a demo Create a Form in the Front End that is bound to a Backend table and have the form close itself automatically after 1 second ( on the Timer Event ). Get another Form to open this form repeatedly
While True
DoCmd.OpenForm "frmTest",,,,,Dialog
Next

Then in Windows explorer look at the BACK END's .ldb file
The file is created and then deleted EVERY time the form opens and closes.


The FIX is to have the form that opens on StartUp bound to a table in the Back End and to leave this form open ( hidden ) for the life of the FrontEnd process.
In that way the BackEnd's .lbd file never gets deleted - so never needs recreating and is hence FAR quicker

Try it.



'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[ghalewood]

Thanks for that, I must admit that my Access/VB skills are limited to this one project, although it is a fairly extensive one.

If I remember correctly, when I split the database into FE/BE, I had 1 form with a few hundred fields to calculate and populate each time the record navigation button was selected, backwards or forwards through the store list.

Given what you have said, I should not have noticed a delay in the screen update (as the form wasn't closed) when the nav button was pressed, but there was. Once I accessed (lol) the BE database directly, the delay was not noticable.

 

[jdttek]

Little Smudge:
VEry helpfull hint.

1. How do you bind a FE form to table in the external (BE) database? Wizard only gives you linked tables to choose.

2. I have two databases linked to to FE (BE1 and BE2). Any harm in this? and is it possible to link tables in the two databases?

Thanks

JDTTEK

 

[LittleSmudge]

JDTTEK

Q1) Sorry - if I confused you.
In the front end you bind to the linked table Icon that you see in the database table view ( the one the wizard show you ) - The point I was making was that if you keep opening and closing the form Access has to keep opening and closing the whole Back End database EACH TIME. So have one for that is bound to any table in the BE and keep it open forever.
( I often use a tblINI in the back end to store set up values that the Admin user 'might' want to adjust. When the front end opens a form bound to this tblINI is opened and hidden. This therefore maintains a permanent link to the BE. Other Forms open and have immediate access to the BE database )


Q2) ONLY TWO !! eeee lad you're not even trying 'ard.

I'm not sure what you are getting at in the second sentence.
Tables in the two databases can be linked into your common Front End and you can the write SQL strings ( or queries ) to JOIN these tables together. Is that what you're asking.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[jdttek]

Thanks for clarifying #1.

On #2, I have been joining thru queries. Is that the same as joining using relationship window so that you can enforce referential integrety? Main concern with two BE's joined ONLY thru SQL / Query, is possible impact on speed. Does seeting up relationships with relationship window set indexes automatically that help speed up queries, sorts and filters?

JDTTEK

 

[LittleSmudge]

Setting up Relationships in the relationships window helps by :-
Allowing the links to be automatically put in when you design queries in QBE grid view.
Allowing you to set up referential integrity, Cascade Updates, Cascade Deletes.

However, I am not aware that it has any effect at all on the speed of operation.

Even if you have two tables joined the Relationship window you CAN create a Query that is based on a completely different join between the same two tables.

Eg
Company
CompanyId PrimeKey
Addr1
Addr2
Addr3
Town
County
PostCode
etc..


tblContact
ContactId PrimeKey
CompanyRef ForeignKey
Addr1
Addr2
Addr3
Town
County
PostCode
etc..

You could set up the above two tables and establish a Relationship
tblCompany.CompanyId = tblContact.CompanyRef
and tag it to Maintain Referential Integrity

You COULD then write
"SELECT tblCompany.*, tblContact.* " _
& "FROM tblCompany INNER JOIN tblContact " _
& "ON tblCompany.PostCode = tblContact.PostCode"

This would be a perfectly valid SQL string that IGNORES the Relationship window linkage and produces set of data where Contact live in the same PostCode area as Companies ( But not necessarily the companies that they work for ).


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Hap007]

Dear NerdCore,

Regarding your original question:
'the relationships in the back end as well as left the original relationships in the front end.'

You only have relationships defined in the backend where the tables are defined.

When you link to those tables in the frontend, you see the relations, but that is because Access is just showing you the relationships as they exist in the backend.

So:
Always define your relationships in the 'Backend'.

Hope This Helps,
Hap



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer