Peachtree Quantum 2010 and Access 2000 ODBC error on linking LineItem table

Posted on 2011-05-03
Last Modified: 2012-05-11
I am using Access 2000 to link to Peachtree Quantum 2010 GL tables.  I can link to every table BUT the LineItem (detail transactions) table.  I get the error "too many indexes".  

I find similar questions posed on different forums, but no solution.  One comment I noticed was that it was the publisher of the pervasive database and not Peachtree that created the problem.  Another said that Access can only handle 32 indexes versus the 33 on the LineItem table.

Anybody have a similar problem and a solution?

Tom Helton
Question by:thelton_us
    LVL 18

    Expert Comment

    If Access is limited to 32 indexes, which it is according to, then having 33 indexes would be beyond that and would return that error.  

    Deleting indexes might cause Peachtree to break.  
    LVL 100

    Expert Comment

    WHy did you include the Crystal Reports zone?

    LVL 28

    Accepted Solution

    Use an ODBC Pass-through Query.  Create a new query, change to the SQL view, then select SQL-Specific/Pass-Through.  Use a simple query like "SELECT * FROM LineItem;" as your query.  This should be able to read the data just fine.  

    You'll probably find that it prompts you for the database name every time.  To fix this, right-click on the query and select Properties.  In the ODBC connection string, add "DSN=dsnname;" where dsnname is the name of your OPDBC DSN to which you want to connect.

    Author Comment

    mlmcc - the ODBC client to connect to Peachtree uses the id and password for the Crystal Reports imbedded in the Peachtree software - figured there might be some connection

    BillBach - we have a winner...except...I can't use the wildcard as it gives a error regarding too many columns.

    I poked around and found a name of one the columns and it works perfectly.

    Now my question - how can I get a list of all the column names?

    LVL 28

    Expert Comment

    by:Bill Bach
    You don't indicate the version of PSQL, but if it is v9.5 or above, then this should work:
       CALL psp_columns(,'LineItem',)

    If that doesn't work, then you can access the data directly with a query like this:
       SELECT * FROM X$Field
       WHERE Xe$File IN (SELECT Xf$Id from X$File WHERE Xf$Name = 'LineItem')
       AND Xe$DataType < 227
       ORDER BY Xe$Offset


    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

    Suggested Solutions

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now