Create Index on .mdb at runtime

Posted on 2004-10-26
Last Modified: 2008-02-01
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
Question by:robneil1
    LVL 44

    Accepted Solution

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

    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.

    Author Comment

    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 run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now