Create Index on .mdb at runtime

I need to check if an index exists on the version of the .mdb i am accessing. If it does not exist then i need to create it permanently in the .mdb .


This is how i am opening the .mdb

Function Opitx()
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
    cnn.Open Trim(App.Path) & "\Awol.MDB"
    Set rs = New ADODB.Recordset
    rs.Open "wol", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
End Function

-----------------------------------------------

thsi is the current bit of code where i need to create the index.
I have used on error to find if the index exists. (is there a better way?)

       rs.Index = ("TITLE")
            If Check3 = 1 Then
                On Error GoTo nocomposer
                rs.Index = ("COMPOSER")
                GoTo composerok
nocomposer:
                On Error GoTo 0
                MsgBox ("No Composer Index , This is an OLD Database, hit enter to continue")
                Text1 = ""
                Text1.Refresh
               
                ' Create the NewIndex
               
**************i need the code to create an index
Index name   composer
Field   comp
I need the index to be permanent
**************
Would not mind to know how to create index with multiple fields  say field names  =  comp  and  title    



Appreiate any help
robneil1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
1. execute a Create Index SQL statement on the database connection object

Example:
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

2. use ADOX to append an index to the table.  If using this method, you will be able to get to the indexes collection directly to be able to detect the existence of the index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robneil1Author Commented:
aikimark,
thanks for your help, i was hoping not to use adox, but i gave it a go, and it does seem much more comprehensive.
regards, robneil1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.