Questions & Answers to Relationships with ADO / ADOX and MDB

[Franky]

Hi everybody,

after a lot of questions to everybody without any satisfieing answer about ADO and Relationships, i contacted a friend from Microsoft who gave me this answer:

If you want to create a Relationship in ADO, it is the best way to have an Index on your table and the index is active. Then just use a SQL-command: SELECT .... WHERE

I asked him, what happens then with ADOX, and he answerred that this was only included for Marketingreasons. ADOX Relations will be anyway translated to an SQL-command, so it is better to do it direct. He said that ADOX is infact only interesting to read informations about the current INDEXes and the structure of the table out. They don't use ADOX for anythign else.

If I am wrong or he is wrong, please reply. I want to clear this Question forever.

But I am still curious about ADOX.
My questions are still the same:

1) I guess that a Key is a Index, so why in all samples (ADOX) they create always a new key/index when they are anyway already created before in a MDB?

2) In all the samples they show always only that they create a new field, with the ADOX.Table If the fields are already there why? I used before other Database-Compilers and when the fields was already there, for example CustNumber in two tables, and the to relate Table had the index correct then it was easy, just say: Set Relation for CustNumber in TableOrders

I hope to get many responses for this to clear this problem forever. Please don't just paste MSDN-code here. This Codes don't bring the solution

Thanks Frank

[gab2001uk]

Here is the DAO code to copy relations from an existing database

Dim dbSrc As Database
    Dim dbNew As Database
    Dim rlSrc As Relation
    Dim rlNew As Relation
    Dim fdSrc As Field
 
    Set dbSrc = OpenDatabase("PathAndFileSource)
    Set dbNew = OpenDatabase("PathAndFileDestination)
 
    For Each rlSrc In dbSrc.Relations
        Set rlNew = dbNew.CreateRelation(rlSrc.Name, rlSrc.Table, rlSrc.ForeignTable, rlSrc.Attributes)
        For Each fdSrc In rlSrc.Fields
            rlNew.Fields.Append rlNew.CreateField(fdSrc.Name)
            rlNew.Fields(fdSrc.Name).ForeignName = rlSrc.Fields(fdSrc.Name).ForeignName
        Next fdSrc
        dbNew.Relations.Append rlNew
    Next rlSrc

Here is the equivalent with ADOX

Dim cnnSrc As New ADODB.Connection
    Dim cnnNew As New ADODB.Connection    
    Dim catSrc As New ADOX.Catalog
    Dim catNew As New ADOX.Catalog
    Dim tblSrc As New ADOX.Table
    Dim tblNew As New ADOX.Table
    Dim keySrc As New ADOX.Key
    Dim keyNew As New ADOX.Key
 
    cnnSrc.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=PathAndFileSource"
    Set catSrc.ActiveConnection = cnnSrc
 
    cnnNew.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=PathAndFileDestination"
    Set catNew.ActiveConnection = cnnNew
 
    'copy relations (keys in ADO)
    For Each tblSrc In catSrc.Tables
        If InStr(1, tblSrc.Name, "MSys", vbTextCompare) = 0 And tblSrc.Type = "TABLE" Then
            For Each keySrc In tblSrc.Keys
                If keySrc.RelatedTable <> "" Then
                    keyNew.Name = keySrc.Name
                    keyNew.Type = adKeyForeign
                    keyNew.RelatedTable = keySrc.RelatedTable
                    keyNew.Columns.Append keySrc.Columns(0).Name
                    keyNew.Columns(keySrc.Columns(0).Name).RelatedColumn = keySrc.Columns(0).RelatedColumn
                    tblNew.Keys.Append keyNew
                    Set keyNew = Nothing
                End If
            Next keySrc
        End If
    Next tblSrc

As Microsoft themselves imply on their site:http://msdn.microsoft.com/library/de...cesstables.asp

You must establish a reference to the Microsoft DAO 3.6 object library and use DAO code to programmatically set these remaining table properties: Description, Filter, OrderBy, LinkChildFields, LinkMasterFields, SubdatasheetExpanded, SubdatasheetName, and SubdatasheetHeight. For information about working with these properties, search Microsoft Access Help.

You are best to use DAO for manipulating database structure!