Change Index Settings Access VBA

1.4k views Asked by At

I'm trying to automate a process in Access, and one of the steps I wish to automate is changing the Indexing settings of certain fields in my tables. I need to do this to increase the speed of subsequent queries (the query is about 100x faster with the indexing).

In any case, suppose my table, called "Cars", looks like this:

ID    Name                 Character     
1     Paul Newman          Doc Hudson
2     Larry the Cable Guy  Mater
3     Owen Wilson          McQueen
4     Joe Ranft            Red

I want to use VBA to change the index settings of "ID" and "Character" from "No" to "Yes (No Duplicates)".

This table does not exist initially. It is created by a query and then I need to index the table and run more queries on it.

1

There are 1 answers

0
BlueBerry On BEST ANSWER

My solution was simply to execute these 2 SQL statements:

CREATE UNIQUE INDEX Index1 ON Cars (ID)
CREATE UNIQUE INDEX Index2 ON Cars (Character)

That did the trick. This is a simple solution.