This project has moved. For the latest updates, please go here.

Suggest to include Errors in database for referenced columns

Sep 1, 2016 at 5:16 PM
In my database I found some records with null data for the referenced column. this was because additional column was added later , and reference was set. So I wrote a simple query to find such records.
'Query 
SELECT MsysRelationships.*, NullRefRecords([szObject],[szReferencedObject],[szColumn],[szReferencedColumn]) AS NullRefRecords, *
FROM MsysRelationships
WHERE (((NullRefRecords([szObject],[szReferencedObject],[szColumn],[szReferencedColumn]))>0));


'Function to return no of records 
Public Function NullRefRecords(fromTable As String, masterTable As String, rfield As String, rfColumn As String) As Integer
    On Error Resume Next
    Dim rst As DAO.Recordset
    Dim sqlString As String

    sqlString = "SELECT Count(" & fromTable & "." & rfColumn & " ) AS CountOfID"
    sqlString = sqlString & " FROM " & masterTable & " RIGHT JOIN  " & fromTable & " ON "
    sqlString = sqlString & masterTable & ".ID = " & fromTable & "." & rfield
    sqlString = sqlString & " WHERE (((" & fromTable & "." & rfield & ") Is Null))"

    Set rst = CurrentDb.OpenRecordset(sqlString)

    NullRefRecords = rst![CountOfID]

End Function
The above code lists all Tables having Null Data in referenced field.
Coordinator
Dec 7, 2016 at 6:58 AM
thanks for sharing!