Creating relationships dynamically

[Dr_Michael]

I have created a program that imports some data to a new Access mdb file from SQL Server. It works well and creates the tables I want, the fields and fills them with the appropriate data. I'am asking now, If I can (through code) to create to this mdb the relationship that I want between two tables for example. Is it possible?

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

if you use ADO for accessing your Jet database, you could use ADOX's object model (=ADO Ext. 2.1 for DDL and Security).
here is a sample from the online docs

Sub CreateKey()

Dim kyForeign As New ADOX.Key
Dim cat As New ADOX.Catalog

' Connect the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"

' Define the foreign key
kyForeign.Name = "CustOrder"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Customers"
kyForeign.Columns.Append "CustomerId"
kyForeign.Columns("CustomerId").RelatedColumn = "CustomerId"
kyForeign.UpdateRule = adRICascade

' Append the foreign key
cat.Tables("Orders").Keys.Append kyForeign

'Delete the Key as this is a demonstration
cat.Tables("Orders").Keys.Delete kyForeign.Name
End Sub