Splitting kills relationships?

[Guest]

Hello,

I split my db into FE and BE, and discovered that the relationship window
for the BE it totally blank.
The one in the FE has lost all its relationships, too - which may be due to
me resetting some autonumbering ID fields by deleting and re-inserting them -
but there at least the tables are still shown.
Is this normal?

Thank you.

 

[Douglas J. Steele]

How did you split the database?

All relationships should be in the BE only: they serve no purpose in the FE.

 

[Allen Browne]

The relationships would have been deleted when you deleted the primary key
field.

The front end will remember the relationships window the way you had
configured it, even though there are no relations to show.

The back end will have nothing in the relationships window. Even if you
click the "Show All", there are no relationships to show, so the window will
remain blank. Yes, this is normal behavior.

To proceed from where you are, you need to create the relationships in the
back end. Be sure to check the box for Referential Integrity (RI) for every
one of your relationships. Creating relations without RI is little more than
an exercise in drawing lines.

If you still have data after deleting and recreating the primary key fields,
the data in the related tables will no longer match the correct records in
the primary tables.

 

[Guest]

Thank you for the explanation.

I deleted all data from the tables prior to the resetting exercise, so there
won't be any issues with non-related records.
All right then, I better go and re-create my relationships.

 

[Tony Toews]

I deleted all data from the tables prior to the resetting exercise, so there
won't be any issues with non-related records.
All right then, I better go and re-create my relationships.

To shorten that work you can copy the relationships window layout from
the original MDB. This is not the relationships themselves. So this
could save you some time.

Use the sysrels utility at the following location to copy the table
relationships layout window
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12
Or use Save Restore Modify Relationship Window at
http://www.lebans.com/saverelationshipview.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[susiedba]

relationships are not as robust in MDB as they are in SQL Server

MDB isn't a viable format; it shouldn't be used by anyone for any
reason
I just use Access Data Projects; it is a lot simpler to deal with

-Susie, DBA

 

[Tony Toews]

relationships are not as robust in MDB as they are in SQL Server

Sure they are. If anything the relationships window is a bit nicer in
Access compared to SQL Server. You have lines that run straight from
field to field in each table in Access. Whereas in SQL Server you
have the lines going around the tables and only from table to table so
you don't know at a glance what field is involved in the relationship
window.

Although maybe that has been fixed in SQL Server 2005.

MDB isn't a viable format; it shouldn't be used by anyone for any
reason
I just use Access Data Projects; it is a lot simpler to deal with

ADPs have their purpose as do MDBs. We've had this discussion a
number of times, Aaron.

-Susie, DBA

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Ron2006]

Amazing...... Aaron/Susie...

A complete post without a single @#$%^&* word. Simply Amazing.

Thank you.

Ron

 

[accesskastle]

I have a question about splitting and relationships. I split my database
using the splitter wizard and I notice that the relationships now appear when
I look at the relationships window in either end (back or front). Does this
use double resources (like something related to indices), or is this more
linking going on in the background that shows up in the foreground? If it
does use additional resources, can I stop it somehow?

The reason why I ask is that I have some code that does the relinking to the
backend tables automatically. It uses the standard CheckLinks code, but it
still seems to take too long to move through the code, check for broken links
and open the database if there are none. I'm wondering if it has to do with
indexed fields (too much? too little?) or relatinships.

AK

RK

 

[Allen Browne]

The relationships exist only in the back end. They are displayed in the
front end for informational purposes only. They are not using up more
resources.

In your relinking code, much of the delay is due to the time it takes to
open the back end for each table. To avoid that, OpenDatabase on the back
end before you link, and close it afterwards. Although you are not actually
using this database object in any way, merely holding it opens speeds up the
process.

This kind of thing:

dim dbData As Database
Set dbData = OpenDatabase("C:\YourFolder\YourBackEnd.mdb")
'put your relinking code here
dbData.Close
Set dbData = Nothing

 

[Tony Toews [MVP]]

I have a question about splitting and relationships. I split my database
using the splitter wizard and I notice that the relationships now appear when
I look at the relationships window in either end (back or front). Does this
use double resources (like something related to indices), or is this more
linking going on in the background that shows up in the foreground? If it
does use additional resources, can I stop it somehow?

No additional resources. Don't worry about it. The only meaningful
relationship diagram is the one in the BE.

The reason why I ask is that I have some code that does the relinking to the
backend tables automatically. It uses the standard CheckLinks code, but it
still seems to take too long to move through the code, check for broken links
and open the database if there are none. I'm wondering if it has to do with
indexed fields (too much? too little?) or relatinships.

The problem is very likely when the second and subsequent users
against the backend. Open a database variable against the backend MDB
and relink all your tables then close the db variable. This keeps the
ldb file open by the FE while it's doing the relinking.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Denise]

Should I add look-up tables to the relationships? And why? Thank you.