what happened to the table relationships?

[rotata]

Ok,

I split my database and put the backend on the server.

I copied the frontend to another location on the server and put some
very basic restrictions on the forms, just enough so that the users do
not accidentally change data.

I kept an unrestricted frontend on my harddrive.

Yesterday, I tried to query a pair of tables and the wizard informed me
that those two tables were not related. I checked my frontend and
discovered that there are no relationships among the tables. I checked
the backend and the relationships are fine.

What do I do now? Am I going to have to re-create all relationships in
both frontends? Do I have to build new frontends?

Jan

 

[Guest]

Jan:

The relationships in the back end are the only ones that really matter.
That's were the constraints are applied. You can still join tables in
queries in the front end. The only difference is that the absence of any
relationships being defined in the front end means that tables are not joined
by default on the key columns when you add them to a query in design view or
via the wizardry. You can still join them manually, however, by dragging
from one to the other in design view in the usual way.

If you really want the functionality of having the relationships defined in
the front end you'll need to redefine them there. You should be able to do
this automatically by returning a reference to the back end with code like
this:

Dim dbs As DAO.Database, dbSource As DAO.Database

strSourcedb = "F:\SomeFolder\MyBackend.mdb"

Set dbs = CurrentDb
Set dbSource = OpenDatabase(strSourcedb)

CopyRelations dbSource

which calls the following function (which is normally used when importing
tables, but should work with linked tables too) to create the relationships
in the front end:

Public Function CopyRelations(dbsSource As Database)

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field, newfld As DAO.Field
Dim rel As DAO.Relation, newrel As DAO.Relation
Dim lngCount As Long, lngN As Long
Dim retVal As Variant

Set dbs = CurrentDb

' rebuild relationships
lngCount = dbsSource.Relations.Count
retVal = SysCmd(acSysCmdInitMeter, "Building Relationships", lngCount)
lngN = 1
For Each rel In dbsSource.Relations
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
' Create new Relation object
Set newrel = dbs.CreateRelation(rel.Name, rel.Table, rel.ForeignTable)
' Set attributes
newrel.Attributes = rel.Attributes
For Each fld In rel.Fields
' Create field in Relation object.
Set newfld = newrel.CreateField(fld.Name)
' Specify field name in foreign table.
newfld.ForeignName = fld.ForeignName
' Append Field object to Fields collection of Relation object.
newrel.Fields.Append newfld
Next fld
' Append Relation object to Relations collection.
dbs.Relations.Append newrel
dbs.Relations.Refresh
Next rel

Exit_Here:
' clear status bar
retVal = SysCmd(acSysCmdClearStatus)
Set dbsSource = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Function

Note that the above uses DAO so you may need to create a reference to the
Microsoft Data Access Objects object library with Tools|Refrences on the VBA
menu bar as since 2000 Access uses ADO as the default data access technology.

Ken Sheridan
Stafford, England

 

[John Vinson]

Ok,

I split my database and put the backend on the server.

I copied the frontend to another location on the server and put some
very basic restrictions on the forms, just enough so that the users do
not accidentally change data.

I kept an unrestricted frontend on my harddrive.

Yesterday, I tried to query a pair of tables and the wizard informed me
that those two tables were not related. I checked my frontend and
discovered that there are no relationships among the tables. I checked
the backend and the relationships are fine.

What do I do now? Am I going to have to re-create all relationships in
both frontends? Do I have to build new frontends?

Jan

The relationships actually exist in the backend; the frontend *should*
inherit the relationships from the backend. Often the Relationships
window will display them differently, or sometimes not at all - but
the window is flaky anyway. Doublecheck that the frontend has the
little arrow icon next to each tablename - might you have Imported the
tables instead of linking them!?

If they are indeed linked try using Tools... Database Utilities...
Linked Table Manager to relink them; or, delete all the tables in the
frontend (just deleting the links of course), and use File... Get
External Data... Link to link to the backend afresh.

John W. Vinson[MVP]

 

[rotata]

Jan:

The relationships in the back end are the only ones that really matter.
That's were the constraints are applied. You can still join tables in
queries in the front end. The only difference is that the absence of any
relationships being defined in the front end means that tables are not joined
by default on the key columns when you add them to a query in design view or
via the wizardry. You can still join them manually, however, by dragging
from one to the other in design view in the usual way.

If you really want the functionality of having the relationships defined in
the front end you'll need to redefine them there. You should be able to do
this automatically by returning a reference to the back end with code like
this:

Dim dbs As DAO.Database, dbSource As DAO.Database

strSourcedb = "F:\SomeFolder\MyBackend.mdb"

Set dbs = CurrentDb
Set dbSource = OpenDatabase(strSourcedb)

CopyRelations dbSource

which calls the following function (which is normally used when importing
tables, but should work with linked tables too) to create the relationships
in the front end:

Public Function CopyRelations(dbsSource As Database)

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field, newfld As DAO.Field
Dim rel As DAO.Relation, newrel As DAO.Relation
Dim lngCount As Long, lngN As Long
Dim retVal As Variant

Set dbs = CurrentDb

' rebuild relationships
lngCount = dbsSource.Relations.Count
retVal = SysCmd(acSysCmdInitMeter, "Building Relationships", lngCount)
lngN = 1
For Each rel In dbsSource.Relations
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
' Create new Relation object
Set newrel = dbs.CreateRelation(rel.Name, rel.Table, rel.ForeignTable)
' Set attributes
newrel.Attributes = rel.Attributes
For Each fld In rel.Fields
' Create field in Relation object.
Set newfld = newrel.CreateField(fld.Name)
' Specify field name in foreign table.
newfld.ForeignName = fld.ForeignName
' Append Field object to Fields collection of Relation object.
newrel.Fields.Append newfld
Next fld
' Append Relation object to Relations collection.
dbs.Relations.Append newrel
dbs.Relations.Refresh
Next rel

Exit_Here:
' clear status bar
retVal = SysCmd(acSysCmdClearStatus)
Set dbsSource = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Function

Note that the above uses DAO so you may need to create a reference to the
Microsoft Data Access Objects object library with Tools|Refrences on the VBA
menu bar as since 2000 Access uses ADO as the default data access technology.

Ken Sheridan
Stafford, England

Ken,

Thank you for the code. I can't generate VBA on my own, but I am
learning to manipulate it. (I store samples in a note-keeper.)

This morning I checked by backend again and found that, not only have
the relationships disappeared, but I cannot re-create them.

I am taking this opportunity to rethink my design and the efficacies of
using a server in which I do not have extensive privileges.

Jan

 

[rotata]

The relationships actually exist in the backend; the frontend *should*
inherit the relationships from the backend. Often the Relationships
window will display them differently, or sometimes not at all - but
the window is flaky anyway. Doublecheck that the frontend has the
little arrow icon next to each tablename - might you have Imported the
tables instead of linking them!?

If they are indeed linked try using Tools... Database Utilities...
Linked Table Manager to relink them; or, delete all the tables in the
frontend (just deleting the links of course), and use File... Get
External Data... Link to link to the backend afresh.

John W. Vinson[MVP]

John,

Thank you for the reply.

Yes, the tables are linked, (little pointy arrows on the left). I don't
think I could do it without the tools and wizards.

The forms, reports and current queries actually work fine. It's just
trying to do anything new that is causing difficulty.

This morning, I found that I couldn't find relationships in either front
or back.

If I create a "new" backend and establish all my relationships, add a
few new tables, and standardize my naming conventions, what sort of
problems might I expect when I link it to the front?

Jan

 

[John Vinson]

Yes, the tables are linked, (little pointy arrows on the left). I don't
think I could do it without the tools and wizards.

The forms, reports and current queries actually work fine. It's just
trying to do anything new that is causing difficulty.

This morning, I found that I couldn't find relationships in either front
or back.

Did you select View All in the Relationships window? Sometimes the
table icons will disappear or move around (even though the
relationships are intact).

If I create a "new" backend and establish all my relationships, add a
few new tables, and standardize my naming conventions, what sort of
problems might I expect when I link it to the front?

Other than having to relink (Tools... Database Utilities... Linked
Table Manager) and adjust your forms, queries and reports to reflect
the new tables and names, you should be fine.

John W. Vinson[MVP]

 

[rotata]

Did you select View All in the Relationships window? Sometimes the
table icons will disappear or move around (even though the
relationships are intact).


Other than having to relink (Tools... Database Utilities... Linked
Table Manager) and adjust your forms, queries and reports to reflect
the new tables and names, you should be fine.

John W. Vinson[MVP]

John,

When I open relationships, the "tables and queries" dialog appears. The
first time it happened, I canceled the dialog and clicked on the
"multi-square" icon - the database shut down completely. The next time,
I used the dialog to add each of the data tables and clicked the "show
relationships" option. Nada. No skinnies, no fats, no 8-ends. No lines
anywhere.

I've got a lot of "can you include this?" requests, so I'm trying to
incorporate those tables and fields.

Thanks again!

Jan

 

[John Vinson]

John,

When I open relationships, the "tables and queries" dialog appears. The
first time it happened, I canceled the dialog and clicked on the
"multi-square" icon - the database shut down completely. The next time,
I used the dialog to add each of the data tables and clicked the "show
relationships" option. Nada. No skinnies, no fats, no 8-ends. No lines
anywhere.

I've got a lot of "can you include this?" requests, so I'm trying to
incorporate those tables and fields.

Hrm. Try copying and pasting this code into a new Module; run it by
typing

Call ShowAllRelations

in the Immediate window. It should show you all the actually stored
relationships.

Have you Compacted and Repaired? What version of Access are you using?

Public Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, _
rel.ForeignTable, Hex(rel.Attributes)
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub



John W. Vinson[MVP]

 

[rotata]

Hrm. Try copying and pasting this code into a new Module; run it by
typing

Call ShowAllRelations

in the Immediate window. It should show you all the actually stored
relationships.

Have you Compacted and Repaired? What version of Access are you using?

Public Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, _
rel.ForeignTable, Hex(rel.Attributes)
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub



John W. Vinson[MVP]

At one time, I had set the db to compact on exit, but the setting
changed on its own. (I usually blame IT updates for such things.)
Haven't yet re-set the control. I do it manually whenever I notice the
file size grow. Do I need to compact front and back ends separately?

I'm running 2003 on xp sp2.


Jan

 

[John Vinson]

At one time, I had set the db to compact on exit, but the setting
changed on its own. (I usually blame IT updates for such things.)
Haven't yet re-set the control. I do it manually whenever I notice the
file size grow. Do I need to compact front and back ends separately?

I'm running 2003 on xp sp2.

<puzzlement> Your relationship issues don't seem to have anything to
do with Compact on Close, and I didn't notice you mentioning
compaction earlier in the thread. Were your relationships OK? Did the
code help?

Generally, one would compact a backend when it doubles in size, or on
some suitable regular schedule. A frontend that's not being actively
worked on (new or revised forms and reports) can simply be discarded
and replaced with a fresh copy, rather than compacted; however, if
you're doing design changes, then compact (AND BACKUP!!) frequently.
The compaction of the frontend and of the backend are completely
independent and unrelated.

John W. Vinson[MVP]

 

[rotata]

<puzzlement> Your relationship issues don't seem to have anything to
do with Compact on Close, and I didn't notice you mentioning
compaction earlier in the thread. Were your relationships OK? Did the
code help?

Generally, one would compact a backend when it doubles in size, or on
some suitable regular schedule. A frontend that's not being actively
worked on (new or revised forms and reports) can simply be discarded
and replaced with a fresh copy, rather than compacted; however, if
you're doing design changes, then compact (AND BACKUP!!) frequently.
The compaction of the frontend and of the backend are completely
independent and unrelated.

John W. Vinson[MVP]

I'm a basket-case when it comes to using code. So far, my biggest
accomplishment is being able to make alternate shaded lines on a report
- and that from a very detailed set of instructions. I'm going to
experiment with what you sent off-line.

No, I don't think the compacting is related to the loss of
relationships. It's a side issue regarding my ongoing battle with our IT
department.

I discarded the obsolete frontend and re-established all relationships
to the new frontend with the linking wizard. I downsized the number of
queries, updated the naming conventions, and reconnected the reports.
All modules now start with frm, tbl, qry, rpt or lbl - with no spaces.
My users won't notice a difference, but I know the db is a lot healthier.

Now, if I can just learn to use VBA...

Thanks!!

Jan