-1

I have a XML file with all the records from all the tables of my DB.

When I have an empty DB with all relationships defined I can read the relationships from the msysrelationships table. Now I would like to know how I can find the correct sequence to import the data.

If I would just import the data as it is presented I could accidentally import data with a reference to not yet existing data. This is a problem.

I have tried a mathematical approach to find the import sequence. I was not able to find a correct function to get the sequence.

Would anyone know how I could make a correct sequence with the info from msysrelationships?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
N. Jans.
  • 1
  • 1
  • How do you expect anyone to answer this question when we know nothing about your data or your code? – Ansgar Wiechers Aug 20 '19 at 08:49
  • The whole point was to not know the data but still understand the relationships within the database. You certainty do have a point regarding the code, my apologies. – N. Jans. Aug 22 '19 at 10:02

1 Answers1

0

I did find a solution.

First we place all found tables in a array. Query by BIBD: How can I get table names from an MS Access Database?

Then we read all the relationships from msysrelationships. Now we order the tableNames by dependencies:

Function getDependencies(database)
  Dim loopBit
  Dim changeFound

  loopBit = True
  getTableNames(database)

  Do While loopbit
    changeCount = 0

    DB_Connect database, "SELECT msysrelationships.szObject AS TableName, msysrelationships.szReferencedObject AS Dependency FROM msysrelationships ORDER BY msysrelationships.szObject;"
    Do Until DB_EndRS = True
      Dim tableNameIndex
      Dim dependencyIndex
      Dim tableParking

      For index = 0 to UBound(tableArray)
        If tableArray(index) = DB_Select("TableName") Then
          tableNameIndex = index
          Exit For
        End If
      Next

      For index = 0 to UBound(tableArray)
        If tableArray(index) = DB_Select("Dependency") Then
          dependencyIndex = index
          Exit For
        End If
      Next

      If tableNameIndex < dependencyIndex Then
        changeCount = changeCount + 1

        tableParking = tableArray(tableNameIndex)
        tableArray(tableNameIndex) = tableArray(dependencyIndex)
        tableArray(dependencyIndex) = tableParking
      End If

      DB_MoveNext
    Loop
    DB_Disconnect

    If changeCount = 0 Then
      loopBit = false
    End If
  Loop
End Function

(I hope this is clear enough, please ask if I should provide extra info.)

This allows me to not know the data in the database while understanding the relationships within the database.

I hope this helps someone!

N. Jans.
  • 1
  • 1