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
:confused:
Printable View
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
:confused:
Take a look at the later posts here:
http://www.vbforums.com/showthread.p...ight=dao+index
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:cool:
Try this:
VB Code:
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... :)
This is clearer (and tested):
VB Code:
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
Thanks PilgrimPete,
I must say Thankyou once more...
You have helped me on several occaisons, for which I am grateful..:D
Whats the best way to call this sub? say if you had this behind a command button.
What do you mean by the best way to call it?
Is this the answer, or have I missed the point completely?
VB Code:
Private sub Button1_Click() CreatePK 'create our primary key End Sub
That works just fine ...