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

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
Who is Participating?
Bill BachPresidentCommented:
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.
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.  
WHy did you include the Crystal Reports zone?

thelton_usAuthor Commented:
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?

Bill BachPresidentCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.