Create Primary Key

[HaydenMB]

Hello,

I am using Accss 2000 and i am using VBA to create a table.

I have created the table fine but I would like to know how I assign one of the fields as a primary key and also create indexes.

Here is the code I have created for the CreateTable:

[red]
Sub CreatePFEPTable()
Dim tdfNew As TableDef
Dim db As Database


Set db = CurrentDb
Set tdfNew = db.CreateTableDef("PFEP&quot

With tdfNew
.Fields.Append .CreateField("Item", dbLong)[/red] [green] ' This is the field I want as Primary Key [/green] [red]
.Fields.Append .CreateField("Carry-Over", dbText, 20)
.Fields.Append .CreateField("GSDB Code", dbText, 15)
.Fields.Append .CreateField("IPF Code", dbText, 15)
.Fields.Append .CreateField("Supplier", dbText, 120)
.Fields.Append .CreateField("Country", dbText, 50)
.Fields.Append .CreateField("City", dbText, 100)
.Fields.Append .CreateField("IPF Part No", dbText, 50)
.Fields.Append .CreateField("Part Name", dbText, 50)
.Fields.Append .CreateField("Deliver to", dbText, 50)
.Fields.Append .CreateField("Pieces Per Car", dbDouble)
.Fields.Append .CreateField("Avg Pieces Per Car", dbDouble)
.Fields.Append .CreateField("Pallet Length", dbDouble)
.Fields.Append .CreateField("Pallet Width", dbDouble)
.Fields.Append .CreateField("Pallet Hight", dbDouble)
.Fields.Append .CreateField("Folded Height", dbDouble)
.Fields.Append .CreateField("Container Code", dbText, 25)
.Fields.Append .CreateField("Packaging Status", dbText, 20)
.Fields.Append .CreateField("Return Type", dbText, 60)


End With

db.TableDefs.Append tdfNew


End Sub
[/red]


Your help will be greatly appreciated,

Cheers,
Hayden

 

[PaulF]

I haven't tested this, but it is based on the CreateIndex example in ACCESS 97, and your code appears to be DAO, not ADO so this should work. BTW... you should explicitly dim the Database Object as DAO.... Dim db As DAO.Database, when using it in 2K or XP, same with Recordsets, will still work if someone moves the ADO library above the DAO library in the References.

Dim idxPK as Index
With tdfNew
Set idxPK = .CreateIndex("PrimaryKey&quot
With idxPK
.Fields.Append .CreateField("Item&quot
End With
.Indexes.Append idxPK
.Indexes.Refresh
End With

PaulF

 

[HaydenMB]

Hi,

thanks for the code, but it is giving me a compile error "Method or data member not found" on the [red].CreateField("Item&quot[/red] part.

Is there anyththing I can change in the references.

Many Thanks,
Hayden

 

[BSman]

OK, I have a dumb question. Why are you creating the table in VBA instead of using the table creation utility that displays the columns and lets you easily modify and add columns as well as indexes and set the primary key?

 

[PaulF]

where did you place the code? It has to be after you've appended the tabledef to the collection and refreshed it so that you can use it. I just ran it in a test, and it worked fine.. BTW the only problem was that it didn't really make it the primary key until I added some more code.. as in this example

Dim db As DAO.database, tdfnew As TableDef
Dim idxPK As Index
Set db = CurrentDb
Set tdfnew = db.TableDefs("IMF&quot
With tdfnew
Set idxPK = .CreateIndex("PrimaryKey&quot
With idxPK
.Fields.Append .CreateField("ITEM_NO&quot
.Primary = True
.Unique = True
End With
.Indexes.Append idxPK
.Indexes.Refresh
End With

PaulF