table filed sizes

[Guest]

I've attempted to change the field size in a table. The particualr field I'm
trying to change is titled course code and its size is 4 characters. I want
to change it to 6 or 8. I keep getting a message "you can't change the data
type or field size of this field, it is part of one or more realtionships.
If you want to change the data type of this field, first delete its
relationships in the relationship window". I've deleted anything in the
relationship that realtes to the course code and I keep getting the message
when I again try to change the field size. HELP! Geting this to change will
make life a lot easier when trying to update records in the future.

 

[Allen Browne]

The function below will show you what relationships you have in your
database, including any hidden ones:

Public Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function
Private Function RelationAttributes(lngAttrib As Long) As String
Dim strOut As String
Dim lngLen As Long
Const dbRelationCascadeNull As Long = &H2000

If (lngAttrib And dbRelationUnique) <> 0& Then
strOut = strOut & "unique, "
End If
If (lngAttrib And dbRelationDontEnforce) <> 0& Then
strOut = strOut & "unenforced, "
End If
If (lngAttrib And dbRelationInherited) <> 0& Then
strOut = strOut & "inherited, "
End If
If (lngAttrib And dbRelationUpdateCascade) <> 0& Then
strOut = strOut & "cascade update, "
End If
If (lngAttrib And dbRelationDeleteCascade) <> 0& Then
strOut = strOut & "cascade delete, "
End If
If (lngAttrib And dbRelationCascadeNull) <> 0& Then
strOut = strOut & "cascade to null, "
End If
If (lngAttrib And dbRelationLeft) <> 0& Then
strOut = strOut & "left join, "
End If
If (lngAttrib And dbRelationRight) <> 0& Then
strOut = strOut & "right join, "
End If
lngLen = Len(strOut) - 2& 'Without trailing comma and space.
If lngLen > 0& Then
RelationAttributes = Left$(strOut, lngLen)
End If
End Function

 

[Guest]

Its possible that one or more relationships may have been created by Access
in addition to those you've created yourself, most likely if you've used the
'look up wizard' when assigning a data type to a field. If you paste the
following procedure into a standard module and call it, the relationships in
your database and the fields used by them will be listed to the debug window
(aka immediate window). You'll then be able to see if there are any which
use the field in question.

Public Sub ListRels()

Dim dbs As DAO.Database
Dim rel As DAO.Relation
Dim n As Integer

Set dbs = CurrentDb

For Each rel In dbs.Relations
Debug.Print rel.Name
Debug.Print "...." & rel.Table
For n = 0 To rel.Fields.Count - 1
Debug.Print "........" & rel.Fields(n).Name
Next n
Debug.Print "...." & rel.ForeignTable
For n = 0 To rel.Fields.Count - 1
Debug.Print "........" & rel.Fields(n).ForeignName
Debug.Print "............................"
Next n
Next rel

End Sub

Note that the code uses DAO so make sure you have a reference to the
Microsoft DAO object library (Tools|References on the VBA menu bar).

If you identify the relationship in question you can delete it by entering
the following in the debug window:

CurrentDb.Relations.Delete "Name of the relationship goes here"

Ken Sheridan
Stafford, England

 

[Guest]

Thanks. Still trying to get to it. I mistakenly clicked on NO as to whether
you helped me or not. If you get that reapons, ignore it for now. I'll keep
you posted.

Thanks again,
Dave

 

[Guest]

I have Windows 97. Does that make a difference with regard to the info you
gave me?

 

[Guest]

I have Windows 97. Does that make a difference with regard to the info you
gave me?

 

[Allen Browne]

Never tried it in Windows 97.

It works with Access 97 though.

 

[Guest]

I think it should work fine. Moreover, Access 97 uses DAO as its default
data access technology, so the reference should already be in place.

Ken Sheridan
Stafford, England