can't change the data type or field size

[Steve Stad]

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.

 

[John W. Vinson]

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

 

[Steve Stad]

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]

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.