Set Primary Field (RESOLVED)

[mohd333]

Hi Everyone,

I have an existing Access databse and I would like to Set an existing field as Primary Key field in Table 1 through VB. I am using VB5/DAO.

Can anyone please Assist..

All your help will be greatly appreciated.

Cheers

[PilgrimPete]

Take a look at the later posts here:
http://www.vbforums.com/showthread.p...ight=dao+index

[mohd333]

Thanks PilgrimPete,

Sorry for being a bit dumb, but I have had a look at the link, and could not realy make any headways...In my case the Table already exists and all I wanto to do is Specify one existing field as as a Primary key (index no duplicates). Could you plz help, do you any simplified sample code...PLZ.plz

Thankx

[PilgrimPete]

Try this:

Sub CreateTable()
    Dim tdf As TableDef
    Dim fld As Field
    Dim idx As Index
    
    'grab the tabledef
    Set tdf = CurrentDb.TableDefs("Table1")
       
    'create an index...
    Set idx = New Index
    With idx
        .Name = "idx_PK"
        .Fields.Append .CreateField("existingField")
        .Primary = True
    End With
    '...and add it to the tabledef
    tdf.Indexes.Append idx
   
End Sub

You'll probably need to tweak it a bit, I haven't tested it...

[PilgrimPete]

This is clearer (and tested):

Sub CreatePK()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim idx As DAO.Index
    
    'grab the database
    Set db = CurrentDb
    
    'grab the tabledef
    Set tdf = db.TableDefs("Table1")
     
    'create an index...
    Set idx = New Index
    With idx
        .Name = "idx_PK"
        Set fld = .CreateField("PKField")   'this creates a reference to the field
        .Fields.Append fld                  'this adds the field to the index - do this as many times as you need
        .Primary = True                     'this sets the index to be a primary key
    End With
    '...and add it to the tabledef
    tdf.Indexes.Append idx
   
End Sub

[mohd333]

Thanks PilgrimPete,

I must say Thankyou once more...
You have helped me on several occaisons, for which I am grateful..

Whats the best way to call this sub? say if you had this behind a command button.

[PilgrimPete]

What do you mean by the best way to call it?

Is this the answer, or have I missed the point completely?

Private sub Button1_Click()
    CreatePK   'create our primary key
End Sub

[mohd333]

That works just fine ...