Help needed in retriveing db reletionships using vba ?

[tony007]

Hi experts. I need a help in retriveing primary key,alternatekey,foreign key and lenght of each field
using vba and displaying it in massage box. I be happy if some one show me how i can retrive these information.
Thanks

[RobDog888]

What db are you using? Access? There are no real foreign keys in access. I have some CodeBank code on creating
a relationship using DAO that should help you. Search by my username in vb6 code bank.

[tony007]

What db are you using? Access? There are no real foreign keys in access. I have some CodeBank code on creating
a relationship using DAO that should help you. Search by my username in vb6 code bank.

Many thanks for u reply. I am using access 2000 and want to use vba to retrive database reletionship. I tried to run your code but i got this error :

run-tim eror'424':

object reuired

---------------------------------------
when i try to debug it it points to this line:

oApp.NewCurrentDatabase App.Path & "\MyDB2.mdb"


----------------------------------------------
i be happy if u help me fix this error. Thanks

Here is the code :

Option Compare Database
 
 
'Add reference to MS Access xx.0 Object Library
'Add reference to DAO 3.x Object Library
Private oApp As Access.Application
 
 
 
Private Sub Command0_Click()
     MsgBox "ok"
    Dim oRel As DAO.Relation
    Dim oDB As DAO.Database
    Dim oTable1 As DAO.TableDef
    Dim oTable2 As DAO.TableDef
    Dim oIndex As DAO.Index
    
    'Create new blank access database
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase App.Path & "\MyDB2.mdb"
    Set oDB = oApp.CurrentDb
    oApp.Visible = True
   
    'Create first table (Table1)
    Set oTable1 = oDB.CreateTableDef("Table1")
    With oTable1
        .Fields.Append .CreateField("Field1", dbInteger)
        .Fields.Append .CreateField("Field2", dbText)
        .Fields.Append .CreateField("Field3", dbText)
        .Fields.Append .CreateField("Field4", dbText)
    End With
    oDB.TableDefs.Append oTable1
    
    'Create an index on Table1
    Set oIndex = oTable1.CreateIndex
    With oIndex
        .Name = "Field1Index"
        .Fields.Append .CreateField("Field1")
        .Primary = True
    End With
    oTable1.Indexes.Append oIndex
    
    'Create second table (Table2)
    Set oTable2 = oDB.CreateTableDef("Table2")
    With oTable2
        .Fields.Append .CreateField("Field1", dbInteger)
        .Fields.Append .CreateField("Field2", dbText)
        .Fields.Append .CreateField("Field3", dbText)
        .Fields.Append .CreateField("Field4", dbText)
    End With
    oDB.TableDefs.Append oTable2
    
    'Create an index on Table2
    Set oIndex = Nothing
    Set oIndex = oTable2.CreateIndex
    With oIndex
        .Name = "Field1Index"
        .Fields.Append .CreateField("Field1")
        .Primary = True
    End With
    oTable2.Indexes.Append oIndex
    
    'Create relationship between table1 and table2
    Set oRel = oApp.CurrentDb.CreateRelation("MyRelationship", "Table1", "Table2", dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
    oRel.Fields.Append oRel.CreateField("Field1")
    oRel.Fields("Field1").ForeignName = "Field1"
    oApp.CurrentDb.Relations.Append oRel
 
    MsgBox "Done!"
    Unload Me
    
End Sub

[RobDog888]

Thats because youur doing it "in" Access. My code example was from VB6 but we can make it work.
Change oApp to Application and remove these lines...

Set oApp = New Access.Application
Private oApp As Access.Application

[Br1an_g]

another post about the same thing by Tony007:

http://www.vbforums.com/showthread.php?t=336554

Tony007, perhaps you should stick to a single post about a single topic, makes it easier for other members to help you.