How to change field properties?

[chec]

Hi,

I have a table which is created by a make table query. I need to make one of the fields in the table a primary key. Thanks.

 

[BillPower]

Open the table in design view. Higlight the field that you want as the Primary Key. (The field cannot have any duplicates). From the menu, select Edit> Primary Key. Save.

You will get an error message if the Field does contain duplicate values and you will be unable to Save.

 

[BillPower]

Just had a thought, if you are running this make table query regularly, then, follow the steps in previous thread, change your make table query to an append query, create a delete query on your made table, which you would run before the append query. Hope that makes sense.

 

[chec]

thanks!! do you know if there's any way to change the property using vba?

 

[BillPower]

This will add a Primary Key to an existing table using DAO:

Dim dbs As Database, TDef As TableDef
Dim Idx As Index, Fld As Field
On Error GoTo Err_CreatePrimary
Set dbs = CurrentDb
Set TDef = dbs.TableDefs("YourTableName&quot 'edit this to your table name
Set Idx = TDef.CreateIndex("MyIndex&quot
Idx.Primary = True
Set Fld = Idx.CreateField("MyIndexedField&quot 'edit this to your field name
Idx.Fields.Append Fld
TDef.Indexes.Append Idx
Err_CreatePrimary:
MsgBox Err.Description

To use this you must have a "Microsoft DAO Object Library" referenced. If you don't know, while in the Visual Basic Editor, from the Menu select Tools> References, if a "Microsoft DAO Object Library" is not checked, scroll down and check one. Choose the one with the highest no.

 

[chec]

Thanks! The code works great!!