Add an index to a table in another Access database

Posted on 2012-09-13
Last Modified: 2012-10-16
From an Access database I create another database and export tables to it. The tables I am exporting are linked in from a backend. To do this I use the following SQL:

"SELECT * INTO myNewTable IN myNewDb FROM myOldTable"

This works fine, except that the newly-created table has no indexed fields. Once the table has been created in the new database I try to create the indexes using this:

"CREATE INDEX myIndex ON myNewTable (myField) IN myNewDb"

It doesn't work. Any ideas?
Question by:TimHudspith
    LVL 39

    Expert Comment

    by:Pratima Pharande
    Please refer the link

    here you will get the tutorial for adding index for the field.
    try this..will help you
    LVL 14

    Accepted Solution


    I use the following DAO code to add an index.  Note you may want to modify the with statement depending on the type of index:

    Public Function AddIndex()
        Dim tdf As DAO.TableDef, fld As DAO.Field, idx As DAO.Index
     'create index on MyField in MyTable
        Set tdf = CurrentDB.TableDefs("MyTable")
        Set idx = tdf.CreateIndex("MyFieldIdx")

    'Note:  MyFieldIdx is just the index name - can be anything unique

        With idx
            .Fields.Append .CreateField("MyField")
            .Unique = False
            .Primary = False
        End With
        tdf.Indexes.Append idx

        db.TableDefs.Append tdf
        Set idx = Nothing
        Set fld = Nothing
        Set tdf = Nothing
    End Function



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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