Cycles and Multiple Cascade Paths

It is possible to create relationships in Access that cannot be converted into relationships in SQL Server, although this rarely happens. If it does happen, you get an error message similar to the following:

The problem is that your relationship includes cascade update or cascade delete actions, which appear to SQL Server to be causing a feedback loop that can lead to problems. The solution is to remove one or more of the cascade actions. If you still need to implement the cascade operation, you can either write a trigger or create a stored procedure to replace it.

In Access, if you have a multi-part foreign key, if you start to enter values for one part of the key, you can only save the record once you have valid values in all parts of the key.
In SQL Server, when you have a multi-part foreign key, you can save the record even if not all parts of the key are complete (some fields in the key can contain NULL values). In fact, you can even have illegal values in parts of the key, because it is only when the key is complete that the values are checked.
This is not a common problem, but it's worthy of a note. The SQL Standards show a MATCH option that applies to defining relationships (or references); Access uses the FULL match option, and SQL Server uses the SIMPLE match option.

Mismatched Fields in Relationships

With Access, you can construct relationships between columns of different data types and sizes; for example, in a Customer table, you can have a foreign key field to a Country table that is Text(20), and in the Country table, the corresponding field could be a Text(30).

SQL Server does not allow this, and you need to modify your design to ensure that both fields are the same size and of the same data type. For the previous example, you can set the foreign key to a Text(30) field.

You could use code based on the following example to detect and possibly autocorrect these problems:

Sub modUpsizeTests_CheckFieldTypesAndSizesInRelationships()
' Check that the fields used in relationships
' are on the same type and size
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fldInRel As DAO.Field
    Dim fld As DAO.Field
    Dim fld2 As DAO.Field
    Dim tdef As DAO.TableDef
    Dim tdef2 As DAO.TableDef
    Set db = CurrentDb
    For Each rel In db.Relations
	For Each fldInRel In rel.Fields
	    Set tdef = db.TableDefs(rel.Table)
	    Set tdef2 = db.TableDefs(rel.ForeignTable)
	    Set fld = tdef.Fields(fldInRel.Name)
	    Set fld2 = tdef2.Fields(fldInRel.ForeignName)
	    If (fld.Type <> fld2.Type) Or (fld.Size <> fld2.Size) Then
		' need to alter one of the fields
		Debug.Print rel.Name & " has a problem with the fields"
	    Else
		Debug.Print rel.Name, tdef.Name, tdef2.Name
	    End If
	Next
    Next
End Sub