Modify field index property using VBA

[dogdays]

Win 7 64, Access 2007.
At run time I would like to change the field index property .
I want to go from indexed (yes(Duplicates OK) to indexed yes(NO Duplicates) and vice versa.

tia Jack

 

[Joe4]

Re: Modify fielld index property using VBA

I am guessing that you may need to first delete that index, then recreate it.
Take a look at these bits of code from Access guru Allen Browne:
https://bytes.com/topic/access/answers/600430-indexed-property
https://bytes.com/topic/access/answers/210649-setting-field-properties-code

 

[xenou]

Re: Modify fielld index property using VBA

Not sure but I think with Access you won't be able to go from "Duplicates allowed" to "No Duplicates allowed" unless there are no duplicates or you remove them first.

 

[dogdays]

Re: Modify fielld index property using VBA

My plan is to load the table with either no index or index, duplicates OK.
Then delete the duplicates and then index no duplicates.

Jack

 

[xenou]

Re: Modify fielld index property using VBA

So did Joe's links help?
ξ

 

[dogdays]

Re: Modify fielld index property using VBA

Yes Joe's links did help.
I finally got it all together, there probably is a more elegant way, but mine worked.
1. Created a copy of the table I would be populating using SQL no indexes present or primary key , without a field named nIdnetity, (All of my tables use nIdentity as primary key with autonumb).
2. Added field nIdentity set as an autonumb field. (could not figure how to alter a field to autonumb (many google searches))
3. Populated table with a select.
4. Dropped all duplicate rows.
5. Created indexes on two fields.
All is well.
Thanks for the help.

Jack