Can't get Stephan Lebans save-restore-modify relationships to work

[terrybell105]

I downloaded Stephan's utility from his website but can't get it to
work - or maybe I'm not driving it properly!
The form works OK with the existing 3 "views" - I can switch between
them and they display changes fine.
But if I add or remove a table from any one of them, strange things
happen.
For example: start with a fresh copy of the downloaded database.
Open the form and click on the "Admin" view.
Right click and click on "Show Table"
Select CustomerSales and Add.
Close the Show Table Dialog box. You'll see the new table added to
the view.
Now try selecting another view, say RelationshipsView1.
On my machine, the new table is stuck on it too.
The same with RelationshipsView2, it is hidden behind customer_4.
Switch back to Admin view.
Assign a new name in the Unique Id text box and click to "Save the
current relationship layout".
Close and reopen the form. The change has disappeared!
If I saved the relationship window , the extra table does get saved,
but when you look at all the views, it's stuck on all of them again.
I guess I must be doing something horribly wrong, because I can't see
anyone else having these problems.

Although, looking at the code, it doesn't look like it's clearing the
relationship window before applying a new view - just repositioning
existing windows.
Would like to get it working - useful for large databases ...
TIA
Terry Bell

[Stephen Lebans]

What version of Access and Windows are you using?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
<te**********@gmail.comwrote in message
news:a4**********************************@a3g2000p rm.googlegroups.com...

>I downloaded Stephan's utility from his website but can't get it to
work - or maybe I'm not driving it properly!
The form works OK with the existing 3 "views" - I can switch between
them and they display changes fine.
But if I add or remove a table from any one of them, strange things
happen.
For example: start with a fresh copy of the downloaded database.
Open the form and click on the "Admin" view.
Right click and click on "Show Table"
Select CustomerSales and Add.
Close the Show Table Dialog box. You'll see the new table added to
the view.
Now try selecting another view, say RelationshipsView1.
On my machine, the new table is stuck on it too.
The same with RelationshipsView2, it is hidden behind customer_4.
Switch back to Admin view.
Assign a new name in the Unique Id text box and click to "Save the
current relationship layout".
Close and reopen the form. The change has disappeared!
If I saved the relationship window , the extra table does get saved,
but when you look at all the views, it's stuck on all of them again.
I guess I must be doing something horribly wrong, because I can't see
anyone else having these problems.

Although, looking at the code, it doesn't look like it's clearing the
relationship window before applying a new view - just repositioning
existing windows.
Would like to get it working - useful for large databases ...
TIA
Terry Bell

[terrybell105]

Xp, Access 2003 - I think service pack 3 but not sure, I'm at home
now ...

[Stephen Lebans]

Save the RelationShip view in Acccess before using my Tools to manipulate
the window.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
<te**********@gmail.comwrote in message
news:8f**********************************@n33g2000 pri.googlegroups.com...

Xp, Access 2003 - I think service pack 3 but not sure, I'm at home
now ...

[terrybell105]

Stephen I've tried your suggestion, it still doesn't seem to work
properly.

I've spent some time changing your test tables to illustrate the
problem, and I've set up a vb function to prepare the test data. If
you execute the vb code below against your download database, it will
do the following:
- Add a new table tblAddress(AddressId,Address)
- Delete all the current relationships (I've done this because you
have a few self-joins in there that complicate the display)
- Added new column Customer.AddressId as a foreign key to the new
address table.
- Added two relations
- one between Customer and CustomerSales, as you originally had.
I've made this a dbRelationDontEnforce type because your data won't
allow enforce.
- the other between tblAddress and Customer
-It also deletes the contents of tblRelationshipViews so we can start
with a clean plate.

To reproduce the problem:
1. Run the code
2. Open and clear the Relationship window.
3. Add all three tables to the window. They will show the relation
lines.
4. Save the Layout (as you suggested)
5. Open your form.
6. Enter Unique Id "AllThree" and click the Save button.
The layout seems to be saved OK
7. Now close the form and open the Relationships window again.
8. Delete one of the tables (eg tblAddress) and save the layout.
9. Leave the layout visble, we want to save this as a second view.
10. Open your form again.
11. Click on the "AllThree" view.

On my machine (incidentally - this one is Win2K and A2K) tblAddress
has disappeared.

Stephen I'm probably having a huge senior's moment over this - very
worrying - so please be patient - but it looks to me like the tool
doesn't seem to be handling different sets of tables correctly.
Changes in table positioning are OK.

I have a large database that I want to split into views for review and
this would be ideal.

Thanks for your time. By the way I've used lots of code from your
site over the years and have the greatest respect for your work -
you've saved me lots of time.

Terry Bell

'***Start code
Option Compare Database
Option Explicit

Function SetupTestConditions()
Dim Sql, rel As Relation, db As Database, fld As Field, tdf As
TableDef, i As Long

'Create new table tblAddress
Set db = CurrentDb()
Sql = "CREATE TABLE tblAddress (AddressId INTEGER CONSTRAINT
AddressIdPK PRIMARY KEY,Address TEXT)"
db.Execute Sql, dbFailOnError

'Delete existing relations, self joins complicate the display
For i = db.Relations.Count - 1 To 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next

'Delete original test relationship diagrams from tblRelationshipViews
db.Execute "Delete from tblRelationshipViews", dbFailOnError

'Add column AddressId to Customer
Set tdf = db.TableDefs("Customer")
tdf.Fields.Append tdf.CreateField("AddressId", dbLong)

'Create relationship tblAddress to Customer
Set rel = db.CreateRelation("AddressToCustomer", "tblAddress",
"Customer", dbRelationUpdateCascade)
rel.Fields.Append rel.CreateField("AddressId")
rel.Fields!AddressId.ForeignName = "AddressId"
db.Relations.Append rel

'Create relationship Customer to CustomerSales.
'Don't try to enforce Ri, Customer 1 doesn't exist.
Set rel = db.CreateRelation("CustomerToCustomerSales", "Customer",
"CustomerSales", dbRelationDontEnforce)
rel.Fields.Append rel.CreateField("CustomerId")
rel.Fields!CustomerId.ForeignName = "CustomerId"
db.Relations.Append rel

MsgBox "Done"

End Function
'*** End Code

[terrybell105]

Stephen ... have you had a chance to look at this?