• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

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
                On Error GoTo 0
                MsgBox ("No Composer Index , This is an OLD Database, hit enter to continue")
                Text1 = ""
                ' 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
1 Solution
1. execute a Create Index SQL statement on the database connection object

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.
robneil1Author Commented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now