Help needed in retriveing db reletionships using vba ?

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

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.

Quote:

---

Originally Posted by 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.

---

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 :

VB 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

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...

VB Code:

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

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.