How to change a table's 'Indexed' from Yes to No in VBA

I need to drop the Primary Key AND remove indexing from the Primary Key field, and have used the following command successfully in the past:

DoCmd.RunSQL "DROP INDEX PrimaryKey ON tbAccounts_WithDuplicatesForReports"

This command NOW drops the PrimaryKey, but the 'Indexed' property remains "Yes (No Duplicates)", but when I used it before, this command also changed the 'Indexed' property to "No".

Recently I have had to reconstruct the table, so something might be different from what it was before, but I was careful to copy all field names from the original, and the names are identical.
David_W_RAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
HI,

pls try
    Set db = CurrentDb
    db.TableDefs("tbAccounts_WithDuplicatesForReports").Indexes.Delete ("PrimaryKey")

Open in new window

Regards
0
 
David_W_RAuthor Commented:
I get a runtime error "Object required" on line:

db.TableDefs("tbAccounts_WithDuplicatesForReports").Indexes.Delete ("PrimaryKey")
0
 
Rgonzo1971Commented:
Hi,

Could you explore the object with Locals

Set Obj = db.TableDefs("tbAccounts_WithDuplicatesForReports")

Open in new window

Regards
0
 
David_W_RAuthor Commented:
I tried it again without the parentheses around "PrimaryKey" thus:

db.TableDefs("tbAccounts_WithDuplicatesForReports").Indexes.Delete "PrimaryKey"



and it ran, removing the Primary Key, but still left the 'Indexed' property of this field as "Yes (No Duplicates)"

Is there a way to selectively change the 'Indexed' property of a (text) field (to "No") ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.