Solved Search for all uses of a key in entire database

[ontopofmalvern]

Hi
I'm doing a bit of house cleaning on a project I use to manage school data. I have come across a duplicate record in my student table (i.e. same student entered twice each with its own auto number PK). Obviously I want to delete one but before doing so I wish to check the PK hasn't been used in any of the tables it is related to. Each student can be referecened by PK in 30+ tables. All the relationships for student ID have 'Cascade Delete and Update checked.

Is there an easy(?) way to search entire database for uses of a particular PK (and maybe even change it when it finds it). This is partly an acadmic question as in this case it is not a herculain task to just have a peak in each table but I think this is the sort of thing I should know how to do and I feel instinctively it should be very doable.

Many thanks for any help.
Richard

 

[theDBguy]

Hi Richard. Did you name all the foreign key fields the same? If so, you could loop through all your tables to check. Otherwise, you could manually create a query joining all your related tables, probably with an outer join, to the students table.

 

[The_Doc_Man]

Having 30+ places to look is what makes this trickier. IF you suspect this is going to be something you have to do often, it will be worth writing some code. If you don't anticipate doing this quite so often, perhaps a brute force or manual method is adequate. Please advise.

 

[ontopofmalvern]

Lucky I did which is a big surprise as when I started this project many moons ago I did break just about every golden rule out there.

I was hoping for something a little more automatic, it is obviously in access somewhere because when you delete cascaded records access finds them all. However I'll give your suggestions a try.

 

[ontopofmalvern]

Having 30+ places to look is what makes this trickier. IF you suspect this is going to be something you have to do often, it will be worth writing some code. If you don't anticipate doing this quite so often, perhaps a brute force or manual method is adequate. Please advise.

Hopefully it won't happen often, I'm going to try theDBguy's solution mainly out of interest, for today's problem brute force I think it is.
Tah

 

[theDBguy]

Lucky I did which is a big surprise as when I started this project many moons ago I did break just about every golden rule out there.

I was hoping for something a little more automatic, it is obviously in access somewhere because when you delete cascaded records access finds them all. However I'll give your suggestions a try.

Hi. It may be automatic for Access, but not for us. Let us know how it goes. Good luck!

 

[HalloweenWeed]

I would run an update query on each of the related tables, replacing the duplicate PK number with the proper one. Is that what you mean by "brute force?"

 

[MajP]

If you create relationships then you can use dao to determine if a field is a foreign key to a primary key. So lets say you have EmployeeID and it is in 30 tables as a foreign key. Then you can write a procedure, loop the tables, loop the fields and find a foreign key to the EmployeeID primary key. Then loop the records to find the occurrences. Sounds like a lot but not that much code. However, this will only work if you establish relations. If no relations is established there is no way to know that it is a foreign key. So you could look the records but you may get the value 123, which is something different than employee ID 123.

 

[The_Doc_Man]

MajP is absolutely right. If there are no formal relationships, you have no "hook" by which to do such a search and must therefore rely on manually programming the searches.

IF you had formal relationships for everything, then you could open MSysRelationships to identify the tables and fields to look for the particular records. Without those formal relationships, this approach would be dead in the water.

I won't describe what you would do unless it is possible because you have relationships. Otherwise, it would just muddy the waters.

 

[ontopofmalvern]

If any one stumbles accross this thread I did find a solution (which is very much in line with The_Doc_Man's suggestion), in the end I sorted my problem with a brute force method as I do not expect it to be a recurrent problem, the truth is the best solution to these problems is not let them occur in the first place. However this is what would work (with corrections to my likely typing errors)

Create a recordset from this query ("tblStudents" is my parent table and "StudentID" is my PK)

SELECT MSysRelationships.szObject AS tbl, MSysRelationships.szColumn AS field
FROM MSysRelationships
WHERE MSysRelationships.szReferencedObject="tblStudents" AND MSysRelationships.szReferencedColumn="StudentID"

The result will be list of tables which import the the PK from from the parent and the name of the field containing the foreign key. Then it is quite simple to loop through all the records of the recordset and run whatever query you need, in my case it would be to UPDATE foriegn key to correct value where the wrong value exists. e.g something like this ("rs" is the recordset created from above query)

"UPDATE " & rs!tbl & " SET [" & rs!field & "] = <newCorrect_ID> WHERE " & rs!field & " = <oldWrong_ID>"

From another thread I started on a similar issue I was directed to this article which I found very useful on understanding how Access stores relationships - of course it all depends as all commentors have said on setting correct relationships at beginning of project.

Relationships1 - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

Many thanks to all contributors to this thread, although I didn't use your suggestions it has taught me stuff I'll use in the future.

 

[theDBguy]

If any one stumbles accross this thread I did find a solution (which is very much in line with The_Doc_Man's suggestion), in the end I sorted my problem with a brute force method as I do not expect it to be a recurrent problem, the truth is the best solution to these problems is not let them occur in the first place. However this is what would work (with corrections to my likely typing errors)

Create a recordset from this query ("tblStudents" is my parent table and "StudentID" is my PK)

SELECT MSysRelationships.szObject AS tbl, MSysRelationships.szColumn AS field
FROM MSysRelationships
WHERE MSysRelationships.szReferencedObject="tblStudents" AND MSysRelationships.szReferencedColumn="StudentID"

The result will be list of tables which import the the PK from from the parent and the name of the field containing the foreign key. Then it is quite simple to loop through all the records of the recordset and run whatever query you need, in my case it would be to UPDATE foriegn key to correct value where the wrong value exists. e.g something like this ("rs" is the recordset created from above query)

"UPDATE " & rs!tbl & " SET [" & rs!field & "] = <newCorrect_ID> WHERE " & rs!field & " = <oldWrong_ID>"

From another thread I started on a similar issue I was directed to this article which I found very useful on understanding how Access stores relationships - of course it all depends as all commentors have said on setting correct relationships at beginning of project.

Relationships1 - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

Many thanks to all contributors to this thread, although I didn't use your suggestions it has taught me stuff I'll use in the future.

Hi. Congratulations! Good luck with your project.

 

[MajP]

This can be done without brute force as I said, but only if you have a relationship established. If not there is no way to say that field is a foreign key to another table.\
I did not put it all together, but you can easily build a procedure and provide to it a PK, a search FK value, and New FK value. Search all tables in the database, if the field is an FK of the PK, then search all records and replace an old value with a new value. All the necessary code is here.

'Module Name: Table Documenter
'Developed by: MajP
'Special Thanks: Skip Vought for help with query 'documentation
'
'Purpose: This Module provides some utilities to help document your database so you can
'better explain it. Often to get help you need
'to explain your tables, fields, and relationships. This can be time consuming.  These
'procedures simplify that process

'Procedures:
'1. DocumentTables: This procedure document all tables and field within each table. It lists
' the field type, primary keys, foreign keys, and indices.
'2. DocumentRelations: This procedure documents all relationships in the database and list the
' table, foreign table, primary keys, and foreign keys.

'How to use:
'1. Place this code in a STANDARD MODULE
'2. Run the procedures from the immediate window.
'3. Post the results forum
'
'************************ Class Code Start ****************************************************

Public Function fncDocumentTables() As String
  Dim strDocument As String
  Dim tblDef As DAO.TableDef
  Dim fld As DAO.Field
  Dim strSql As String
  For Each tblDef In CurrentDb.TableDefs
    If Not Left(tblDef.Name, 4) = "MSys" Then
      strDocument = strDocument & vbCrLf & tblDef.Name & vbCrLf
      For Each fld In tblDef.Fields
        strDocument = strDocument & "   " & fld.Name & "   " & fncFldTypeToString(fld.Type)
        If isPK(tblDef, fld.Name) Then
          strDocument = strDocument & "  PrimaryKey"
        End If
        If isFK(tblDef, fld.Name) Then
          strDocument = strDocument & "  ForiegnKey"
        End If
        If isIndex(tblDef, fld.Name) Then
          strDocument = strDocument & "  Indexed"
        End If
        If fld.required Then
           strDocument = strDocument & "  Required"
        End If
        strDocument = strDocument & vbCrLf
      Next fld
     End If
  Next tblDef
  fncDocumentTables = strDocument
End Function

Public Function WriteTables() As String
  Dim strDocument As String
  Dim tblDef As DAO.TableDef
  Dim fld As DAO.Field
  Dim strSql As String
  Dim tableName As String
  Dim fieldName As String
  Dim strDataType As String
  Dim keyType As String
  Dim strIndexed As String
  Dim strRequired As String
  strSql = "INSERT INTO tblTables (TableName,FieldName,DataType,KeyType,Indexed, Required) Values ("
  For Each tblDef In CurrentDb.TableDefs
    If Not Left(tblDef.Name, 4) = "MSys" Then
      strDocument = strDocument & vbCrLf & tblDef.Name & vbCrLf
      For Each fld In tblDef.Fields
        strDocument = strDocument & "   " & fld.Name & "   " & fncFldTypeToString(fld.Type)
        If isPK(tblDef, fld.Name) Then
          strDocument = strDocument & "  PrimaryKey"
        End If
        If isFK(tblDef, fld.Name) Then
          strDocument = strDocument & "  ForiegnKey"
        End If
        If isIndex(tblDef, fld.Name) Then
          strDocument = strDocument & "  Indexed"
        End If
        If fld.required Then
           strDocument = strDocument & "  Required"
        End If
        strDocument = strDocument & vbCrLf
      Next fld
     End If
  Next tblDef
  fncDocumentTables = strDocument
End Function

Public Function fncFldTypeToString(intFieldType As Integer) As String
  Select Case intFieldType
    Case 1
      fncFldTypeToString = "dbBoolean"
    Case 2
      fncFldTypeToString = "dbByte"
    Case 3
      fncFldTypeToString = "dbInteger"
    Case 4
      fncFldTypeToString = "dbLong"
    Case 5
       fncFldTypeToString = "dbCurrency"
    Case 6
      fncFldTypeToString = "dbSingle"
    Case 7
      fncFldTypeToString = "dbDouble"
    Case 8
      fncFldTypeToString = "dbDate"
    Case 9
      fncFldTypeToString = "dbBinary"
    Case 10
      fncFldTypeToString = "dbText"
    Case 11
      fncFldTypeToString = "dbLongBinary"
    Case 12
      fncFldTypeToString = "dbMemo"
    Case 13
      fncFldTypeToString = "Text"
    Case 14
      fncFldTypeToString = "Text"
    Case 15
      fncFldTypeToString = "dbGUID"
    Case 16
      fncFldTypeToString = "dbBigInt"
    Case 17
      fncFldTypeToString = "dbVarBinary"
    Case 18
      fncFldTypeToString = "dbChar"
    Case 19
      fncFldTypeToString = "dbNumeric"
    Case 20
      fncFldTypeToString = "dbDecimal"
    Case 21
      fncFldTypeToString = "dbFloat"
    Case 22
      fncFldTypeToString = "dbTime"
    Case 23
      fncFldTypeToString = "dbTimeStamp"
  End Select
End Function

Public Function isPK(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
    If idx.Primary Then
      For Each fld In idx.Fields
        If strField = fld.Name Then
          isPK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function

Public Function isIndex(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
     For Each fld In idx.Fields
        If strField = fld.Name Then
          isIndex = True
          Exit Function
         End If
      Next fld
  Next idx
End Function

Public Function isFK(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
    If idx.Foreign Then
      For Each fld In idx.Fields
        If strField = fld.Name Then
          isFK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function


Public Function fncDocumentRelations() As String
  Dim strDocument As String
  Dim rel As DAO.Relation
  Dim fld As DAO.Field
  Dim idx As DAO.Index
  Dim prop As DAO.Property
  For Each rel In CurrentDb.relations
      strDocument = strDocument & vbCrLf & "Name: " & rel.Name & vbCrLf
      strDocument = strDocument & "  " & "Table: " & rel.Table & vbCrLf
      strDocument = strDocument & "  " & "Foreign Table: " & rel.ForeignTable & vbCrLf
      For Each fld In rel.Fields
        strDocument = strDocument & "  PK: " & fld.Name & "   FK:" & fld.ForeignName
        strDocument = strDocument & vbCrLf
      Next fld
  Next rel
  fncDocumentRelations = strDocument
 
End Function

Public Function fncDocumentQueries() As String
  Dim strDocument As String
  Dim qryDef As DAO.QueryDef
  Dim fld As DAO.Field
  Dim idx As DAO.Index
 
  For Each qryDef In CurrentDb.QueryDefs
    If Not (Left(qryDef.Name, 4) = "MSys" Or Left(qryDef.Name, 4) = "~sq_") Then
      strDocument = strDocument & vbCrLf & qryDef.Name & vbCrLf
      For Each fld In qryDef.Fields
        strDocument = strDocument & "   " & fld.Name & "   " & fncFldTypeToString(fld.Type)
        strDocument = strDocument & vbCrLf
      Next fld
        strDocument = strDocument & qryDef.SQL & vbCrLf
     End If
  Next qryDef
  fncDocumentQueries = strDocument
End Function

Public Sub DocumentTables()
  Debug.Print fncDocumentTables
End Sub

Public Sub DocumentRelations()
  Debug.Print fncDocumentRelations
End Sub

Public Sub DocumentQueries()
  Debug.Print fncDocumentQueries
End Sub

I use this module to document my db to post examples, and use the sub functions in many other applications. Here is where I document relations.

Name: CategoriesProducts
  Table: Categories
  Foreign Table: Products
  PK: CategoryID   FK:CategoryID

Name: OrdersOrder Details
  Table: Orders
  Foreign Table: Order Details
  PK: OrderID   FK:OrderID

Name: ShippersOrders
  Table: Shippers
  Foreign Table: Orders
  PK: ShipperID   FK:ShipVia

Name: SuppliersProducts
  Table: Suppliers
  Foreign Table: Products
  PK: SupplierID   FK:SupplierID

So as you can see you can find all the PK to FK relationships. So very easily you can use this code to automate the rest of the process. From the above code I know everything. The PK to FK, the name of the FK, the table with the FK.

 

[MajP]

The document tables function can provide a lot of info about your tables.

Categories
   CategoryID   dbLong  PrimaryKey  Indexed
   CategoryName   dbText  Indexed  Required
   Description   dbMemo
   Picture   dbLongBinary

Products
   ProductID   dbLong  PrimaryKey  Indexed
   ProductName   dbText  Indexed  Required
   SupplierID   dbLong  ForiegnKey  Indexed
   CategoryID   dbLong  ForiegnKey  Indexed
   QuantityPerUnit   dbText
   UnitPrice   dbCurrency
   UnitsInStock   dbInteger
   UnitsOnOrder   dbInteger
   ReorderLevel   dbInteger
   Discontinued   dbBoolean

Customers
   CustomerID   dbText  PrimaryKey  Indexed
   CompanyName   dbText  Indexed  Required
   ContactName   dbText
   ContactTitle   dbText
   Address   dbText
   City   dbText  Indexed
   Region   dbText  Indexed
   PostalCode   dbText  Indexed
   Country   dbText
   Phone   dbText
   Fax   dbText

Employees
   EmployeeID   dbLong  PrimaryKey  Indexed
   LastName   dbText  Indexed  Required
   FirstName   dbText  Required
   Title   dbText
   TitleOfCourtesy   dbText
   BirthDate   dbDate
   HireDate   dbDate
   Address   dbText
   City   dbText
   Region   dbText
   PostalCode   dbText  Indexed
   Country   dbText
   HomePhone   dbText
   Extension   dbText
   Photo   dbText
   Notes   dbMemo
   ReportsTo   dbLong

 

[ontopofmalvern]

Big thanks to MajP.