Create a new Table in VB

[MarionD]

Good Morning to All,

I hope someone can give me a hand here!

I am trying to do an “automatic” Version Update on my backend DB. I am using the following code to create a new Table. My only problem is that I can’t seem to set the primary key. It does create a unique index for “RID” but doesn’t have the little key symbol next to it! Why not?
And the next problem is how to create a “double key” eg. I have a table where the key should be a combination of Staff_ID and Form_ID

Set td = db.CreateTableDef("Reihentbl")
Set fld = td.CreateField("RID", dbLong)
fld.Required = True
fld.Attributes = dbAutoIncrField
td.Fields.Append fld
Set idx = td.CreateIndex("RID") 'Primary key
idx.Unique = True
Set fld = idx.CreateField("RID", dbLong)
idx.Fields.Append fld
td.Indexes.Append idx
Set fld = td.CreateField("Reihe", dbText, 5)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
Set fld = td.CreateField("RBezeichnung", dbText, 100)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
Set fld = td.CreateField("BFID", dbLong)
td.Fields.Append fld
db.TableDefs.Append td

 

[AncientOne]

Dear Marion,

after creating the index, add the line;

idx. primary=true

(this automatically sets the index to be unique)

For the second answer see here;


http://support.microsoft.com/default.aspx?scid=kb;en-us;141612

 

[Keith P]

Public Sub CreateNewTable()
On Error Resume Next

Dim myDb As DAO.Database
Dim tableName As TableDef
Dim indexNew As Index

Set myDb = OpenDatabase("C:\MyFolder\myDataBase.mdb")
Set tableName = indDb.CreateTableDef("MyNewTableName")

With tableName
.Fields.Append .CreateField("Id", dbLong)
.Fields.Append .CreateField("myField1", dbLong)
.Fields.Append .CreateField("myField2", dbLong)
.Fields.Append .CreateField("myField3", dbBoolean)
.Fields.Append .CreateField("myField4", dbText, 12)
.Fields.Append .CreateField("myField5", dbMemo)

.Fields![Id].Attributes = dbAutoIncrField

.Fields.Refresh

Set indexNew = .CreateIndex("IdIx")
With indexNew
.Fields.Append .CreateField("Id")
.Fields.Append .CreateField("myField1")
.Primary = True
End With
.Indexes.Append indexNew
.Indexes.Refresh

Set indexNew = .CreateIndex("AnotherIx")
With indexNew
.Fields.Append .CreateField("myField2")
.Primary = False
.Unique = True
End With
.Indexes.Append indexNew
.Indexes.Refresh

End With

myDb.TableDefs.Append tableName
myDb.Close

End Sub

I hope this helps

 

[MarionD]

Thanks so much to both of you. Really don't know why I missed the .primary! Keith your code is so much neater than mine!

Thanks again

Bye for now