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