[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Does VFPODBC.dll have a LIMIT on CDX size ?

Posted on 2008-11-12
9
Medium Priority
?
852 Views
Last Modified: 2012-06-22
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 ?
0
Comment
Question by:bcreen
  • 5
  • 3
9 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22945603
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
 
LVL 2

Expert Comment

by:bchesley
ID: 22966498
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22966744
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:bchesley
ID: 22966903
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22966984
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
 
LVL 2

Expert Comment

by:bchesley
ID: 22967107
I will let bcreen decide if my input was useful or relevant.  :-)
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22967342
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
 

Accepted Solution

by:
bcreen earned 0 total points
ID: 23344030
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 23345579
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

872 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