I am creating/editing updating fields in table design. I am trying to change
the field size of the first field. But getting message.. 'You can't change
the data type or field size of this field, it is part of on or more
relationships. I deleted the relationship and still get the message. Can I
screw anything up if I delete any other relationships. Or does access
re-connect all relationships on demand.
Thanks for any replies.

On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad
<(E-Mail Removed)> wrote:

>I am creating/editing updating fields in table design. I am trying to change
>the field size of the first field. But getting message.. 'You can't change
>the data type or field size of this field, it is part of on or more
>relationships. I deleted the relationship and still get the message. Can I
>screw anything up if I delete any other relationships. Or does access
>re-connect all relationships on demand.
>Thanks for any replies.

You may have a hidden relationship; for example, Lookup fields create
relationships which may not show in the relationships window. Also, the
relationships window can be misleading: if you just delete a table icon, the
join line disappears but the relationship doesn't. You must select and delete
the *join line itself*.

Try the "Show All Relationships" icon on the relationships window, or call
this code from the Immediate window:

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
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub
--

John W. Vinson [MVP]

John - Thanks for quick reply. I tried this code in the immediate window.
....to 'call
this code from the Immediate window'?? do I compile??...step into??...run
macro??? sorry i am a newbie particualy w/code. What should I expect to
see? I opened the imediate window from blank form/design mode.

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


"John W. Vinson" wrote:

> On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad
> <(E-Mail Removed)> wrote:
>
> >I am creating/editing updating fields in table design. I am trying to change
> >the field size of the first field. But getting message.. 'You can't change
> >the data type or field size of this field, it is part of on or more
> >relationships. I deleted the relationship and still get the message. Can I
> >screw anything up if I delete any other relationships. Or does access
> >re-connect all relationships on demand.
> >Thanks for any replies.
>
> You may have a hidden relationship; for example, Lookup fields create
> relationships which may not show in the relationships window. Also, the
> relationships window can be misleading: if you just delete a table icon, the
> join line disappears but the relationship doesn't. You must select and delete
> the *join line itself*.
>
> Try the "Show All Relationships" icon on the relationships window, or call
> this code from the Immediate window:
>
> 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
> For Each fld In rel.Fields
> Debug.Print fld.Name; " linked to "; fld.ForeignName
> Next fld
> Next rel
> End Sub
> --
>
> John W. Vinson [MVP]
> .
>

On Mon, 11 Jan 2010 13:41:02 -0800, Steve Stad
<(E-Mail Removed)> wrote:

>John - Thanks for quick reply. I tried this code in the immediate window.
>...to 'call
>this code from the Immediate window'?? do I compile??...step into??...run
>macro??? sorry i am a newbie particualy w/code. What should I expect to
>see? I opened the imediate window from blank form/design mode.

Sorry! Too brief there on my part.

Open the database. Find the Modules tab and create a new module. Copy and
paste


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

into a new Module. Click Debug.Compile from the Menu; post back if you get any
errors.

Save the module using some name OTHER than ShowAllRelations - basRelations
maybe.

Then type Ctrl-G if the immediate window isn't visible. When it is, type

Call ShowAllRelations

into the immediate window.

You should see a scrollable list of all your table relationships.
--

John W. Vinson [MVP]