Table creation in ACCESS 2000 MDB

[Dr_Evil]

Have you tried defining only one primary key for the table? Access doesn't allow you to define any more than this.

[Patrice Bourdages]

After long testing and scrubbing, I finally got it. At least, it works..

For the database creation:

    Dim cat As New ADOX.Catalog
    Dim Vehicule As New ADOX.Table
    Dim Essence As New ADOX.Table
    Dim Entretien As New ADOX.Table
    Dim CodeEntretien As New ADOX.Table
    Dim idx As New ADOX.Index
    Dim kyForeign As New ADOX.Key
    '*
    '* Création de la base de données si inexistante
    '*
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=d:\projets\voiture2\data\tt.mdb;"

For creating a table:

    With Essence
        .Name = "Fuel"
        Set .ParentCatalog = cat
        .Columns.Append "NoVehicule", adInteger
        .Columns.Append "DateAchatEssence", adDate
        .Columns.Append "Sequence", adInteger
        .Columns("Sequence").Properties("AutoIncrement") = True
        .Columns.Append "TypeQteEssence", adVarWChar, 1
        .Columns.Append "QteEssence", adSingle
        .Columns.Append "CoutEssence", adCurrency
        .Columns.Append "OdometreEssence", adSingle
        .Columns.Append "PetitOdometreEssence", adSingle
        .Columns.Append "LieuAchat", adVarWChar, 50
    End With
    cat.Tables.Append Essence

And for the index / primary key:

    With idx
        .Name = "FuelIndex"
        .PrimaryKey = True
        .Columns.Append "NoVehicule"
        .Columns.Append "DateAchatEssence"
        .Columns.Append "Sequence"
    End With
    Essence.Indexes.Append idx

As for link between two tables, here it is:

    kyForeign.Name = "FuelVehicule"
    kyForeign.Type = adKeyForeign
    kyForeign.RelatedTable = "Vehicule"
    kyForeign.Columns.Append "NoVehicule"
    kyForeign.Columns("NoVehicule").RelatedColumn = "NoVehicule"
    kyForeign.UpdateRule = adRICascade
    kyForeign.DeleteRule = adRICascade
    cat.Tables("Fuel").Keys.Append kyForeign
    Set kyForeign = Nothing

Since a lot of people have helped me in the past few months, it's just fair to post my findings in order to help others.

And Dr_Evil, It's true that could only have one primary key per table, but you can have up to ten indexes with up to ten columns per indexes.

Life is great :-)
VB is great ;-)

See ya all