This is a public Forum  publicRSS

Topic

    JoshR
    'Too many indexes' Access error via ODBC on Feb...
    Topic posted June 15, 2010 by JoshR , last edited January 9, 2012
    6495 Views, 3 Comments
    Title:
    'Too many indexes' Access error via ODBC on Feb '10
    Content:

    We're about a month out from our Feb '10 upgrade cutover, and our Reporting gurus here are testing out some existing MS Access connectivity that we have via ODBC, but against the Feb '10 upgrade site, to work out any compatibility/schema kinks prior to the upgrade.

    Our Reporting guru, upon trying to link to the tables on our Upgrade site from Access, got an error on several tables that:

    "The operation failed.  There are too many indexes on table '<table_name>'.  Delete some of the indexes on the table and try the operation again."

    Most of the tables came down, but a respectable handful (including 'incidents') threw the above error when Access tried to link to the table.

    We have several business-critical reports that are generated via Access & ODBC, so this is obviously a huge concern for us; hoping someone had seen this before & knew a fix, or had some insight on where to start troubleshooting.  Obviously I doubt Hosting & the DBAs would be too keen on remove indexes from the table. :)

    Using MS Access 2003 (SP3) and the 5.1.6 MySQL ODBC/Connector driver for the connection.

    Help!

    Answer

     

    • Sophistical

      The only solution I'm aware of is to use a different application other than Access to link in the tables. 

      This is funny; I was just searching Google for the "too many indexes" error when I found this post as the first result.  I was looking for some details on the error to send an example link to my boss to help justify my business need to get access to the Enterprise tool (Business Objects Crystal Enterprise) that we use internally to get around the Access limitation.

      Other "ad-hoc" tools like this may work; I know our developers use it for queries and it may meet your need.

    • Sophistical

      Let me modify my recommendation.  I'd suggest MySQL Workbench or possibly BenthicSQALL after doing a little more research.

    • fiazi

      Use a "Pass Through Query" as advised in here:

      http://www.access-programmers.co.uk/forums/archive/index.php/t-123550.html

      Exact steps on how to do pass through query is obtained in here:

      http://support.microsoft.com/kb/303968

      What is the pass through query?

      SQL pass-through queries are used to send commands directly to an ODBC database server. By using an SQL pass-through query, you work directly with the server tables instead of having the Microsoft Jet database engine process the data.

      --

      I was stuck with the exact same problem as you did.. connecting to a Oracle Database through ODBC from MS Access 2010. And, the table had too many indexes.. and I got the above error message.

      But, now that I have written the query using "Pass Through Query".. and save the query... I don't have any more issues.

      Hope this helps someone.

      It is a wonderful thing to share knowledge on discussion forums.

      Thank you all.

      from Bangkok, Thailand

     

    All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.