Does VFPODBC.dll have a LIMIT on CDX size ?

We've successfully run MS Access (2003) for years against an accounting system whose files are stored in FoxPro 3.0 format (using a *.DBC - database container).  Some payroll history tables have grown quite large, and now MS Access returns an "invalid index" error message from the ODBC driver.  The same table/index for other 'divisions' within the payroll system still open fine.  It seems that just the divisions with over about 250MB-sized compound index files (CDXs) are effected.
Other than removing older data, does anyone know of a workaround or fix ?
bcreenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
the limitation of foxpro files have always been 2GB, not 250 mb, but corruption get's more likely, the larger the files are. It pays to have maintainance do Reindex and Pack foxpro tables to create the index from scratch. Indexes kind of get 'used', fragmented.

Bye, Olaf.
0
bchesleyCommented:
Yes, you likely have index file corruption in the foxpro based system.  There should be a utility function in the VFP application to pack and reindex the applications tables.  I would run this first.

FYI - the native pack and reindex commands in foxpro don't always work as expected from a developer perspective and can be a source of some grief in managing the integrity of the tables and their associated metadata (stored in the dbc container files) so many vfp developers use a third party library to replace these native functions - you can find out more at http://www.stonefield.com/sdt.aspx.

Regards,

Bob
0
Olaf DoschkeSoftware DeveloperCommented:
Bob, while this is not the whole truth Stonefield is  a recommendable tool, it does work better as it backs up the meta data while it's good. The dbc for example does not store index expressions used for the tags, so reindex can fail if the cdx file header containing the index definitions is corrupt and not only the index data after the header area. That's the only secret of stonefield, but in itself the reindex commands and also pack etc have no error or are less good.

Bye, Olaf.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bchesleyCommented:
Olaf - fair enough.  As a developer I would not deploy any significant VFP application without it, but it is a VFP programmer's tool and may not be relevant to this question.

A little additional information from the SDT web site - "Visual FoxPro's data dictionary provides table and field validation, field captions, triggers, even table and field comments.

Unfortunately, many things are missing from the data dictionary, such as structural information necessary to create or update table structures at client sites and useful information such as captions for tables and indexes. Also, the tools Visual FoxPro provides to manage the database container itself are less robust than you'd expect. For example, altering the structure of a table breaks views based on that table. Table and field name changes aren't propagated throughout the database, resulting in orphaned database objects. Moving a table to a different directory or changing the DBF name causes the database to lose track of the table."

Cheers

Bob
0
Olaf DoschkeSoftware DeveloperCommented:
Bob,

altogether not totally wrong but you show you don't know VFP thoroughly. For example copying the DBC file alone - true - makes it loos track of it's DBFs, as it stores relatives path as soon as they can be relative, but that allows you to move a database including it's tables to somewhere else making it work from there. You're having many halfbaked knowlegde there.

Bye, Olaf.
0
bchesleyCommented:
I will let bcreen decide if my input was useful or relevant.  :-)
0
Olaf DoschkeSoftware DeveloperCommented:
Going back to the original question. I'd suggest creating a backup of the dbf,cdx,fpt file of the offending table then try a REINDEX. If that would not fix the index you need a lower level of "resurrection" of the index file: Delete the tags and recreate them from scratch. But you'd need to know the index types and expressions for that. You may get these with KEY().

Both of these functions will be available by using the VFP OLEDB Provider. If you doenload it it comes with a help file and sample data and code to access data by .NET and Office Applications (including Access).

Bye, Olaf.
0
bcreenAuthor Commented:
Friends,

No one answered the actual question: it concerned an ODBC drivers' inability to handle FoxPro CDX index files larger than about 250MB.... never did get a usable answer, as everyone was bickering about their knowledge of FoxPro itself, or tools to deploy with FoxPro applications -- all IRRELEVANT to my ODBC driver question.  So I simply removed records from the large history table, until the reindexed CDX was smaller than 200MB, and that was a temporary workaround that I mentioned in my initial question.

When I find a solution/workaround OUTSIDE of this forum, I absolutely CANNOT figure out how to RETRACT, COMPLETE, DELETE, CLOSE or otherwise indicate that this problem is no longer of interest ! ! ! !

Could someone send me an offline email (hello customer service !) and tell me exactly HOW to do this?   I'm not a stupid person... am I missing something that's OBVIOUS to everyone else?

So please stop the emails bugging me that I have 'open questions', and tell me how the heck to CLOSE them !

Thanks, and Happy New Year to all !    Bill Creen   BillCreen@GMail.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
My initial posting directly adressed your question. I said the limitation of foxpro files have always been 2GB, not 250 mb. I believe you what you observe, but it won't have anything to do with file size limitations of foxpro, and whether ODBC comes into play or not does not change this.

Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.