Setting table property in VB

[Stephen]

Is there a way to change a table property through a VB code command.

Example:

I have a table called "tblCoupons"

One of the fields in this table is a called "CouponNumber". The Indexed
Property for this field is set to "Yes (No Duplicates)"

I want to run VB Code that will change the Property of this field to "Yes
(Duplicates OK)".

Is there a way to do thisin VB? I know I can just open the table in design
view and change it, but for reasons I won't go into (because it will be moot
for this discuss and thus waste your time, which I deeply value) I need to
do it in VB.

Thanks in advance.

-Stephen

 

[Allen Browne]

The property you mention is actually a property of the Indexes collection of
the TableDef.

If you have a reference to the DAO library for the version of JET that
matches your Access database, you can set the Unique property of the Index
to No.

 

[Stephen]

Allen -

Would you help me with the syntax to run this in VB?

set db = currentdb

........

 

[Stephen]

Allen -

This is my attempt, but I know there are errors:

Function settableindex()

Dim db As DAO.Database

Dim tdfCoupons As TableDef
Dim idxCouponNumber As Index

Set db = CurrentDb

With db
Set tdfCoupons = .TableDefs!tblCoupons
With tdfCoupons
Set idxCouponNumber = .Indexes("CouponNumber")
idxCouponNumber.Unique = False
End With
End With

End Function

 

[Allen Browne]

Hmm. Looks like you have to delete and recreate it.

This example assumes a table named "MyTable", with a unique Index named
"Surname" on the field named "Surname". (The index doesn't have to have the
same name as the field, but that's the default behaviour in Access.)

The code deletes the existing index, and replaces it with one the same name
that does not have the Unique property set:

Function ChangeIndex()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")

tdf.Indexes.Delete "Surname"
Set ind = tdf.CreateIndex("Surname")
ind.Fields.Append ind.CreateField("Surname")
tdf.Indexes.Append ind

Debug.Print ind.Unique

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[Stephen]

Allen -

Worked GREAT! Thank you.

-Stephen

Hmm. Looks like you have to delete and recreate it.

This example assumes a table named "MyTable", with a unique Index named
"Surname" on the field named "Surname". (The index doesn't have to have the
same name as the field, but that's the default behaviour in Access.)

The code deletes the existing index, and replaces it with one the same name
that does not have the Unique property set:

Function ChangeIndex()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")

tdf.Indexes.Delete "Surname"
Set ind = tdf.CreateIndex("Surname")
ind.Fields.Append ind.CreateField("Surname")
tdf.Indexes.Append ind

Debug.Print ind.Unique

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function