Foxpro VP 6 Packing

Posted on 2012-04-05
Medium Priority
Last Modified: 2012-04-10
Hello we have a Visual Foxpro application we wrote in house years ago for managing inventory and customers, work tickets. It serves us well, but we have not done any maintenance for at least 5 years.
There are about 6 main table ranging in size up to 15MB.
I have noticed at least once where a report brings up no data for a specific part, however have verified that the part does show on the work ticket.

So my thoughts are that we probably need to re index and pack.

I have been trying to find someone to come in and do this but the one programmer I found could find his way around but was not real familiar with Foxpro. I kept saying I wanted to reindex the tables but he said Foxpro would do this automatically. Later I read about Pack and Index commands.
I think this is a simple process, basically running some commands on the tables and then copy them back to the server directory. Correct me if I am wrong.

So I have come to the experts!

Looking for some simple direction on what I need to do to do this.
Should I index? What is the process?
Should I pack? What is the process?
Anything else I need to do for maintenance?
Anything I need to be cautious of other than the usual make backup first etc?

Is there a program I can buy that would take care of this for me? I have seen various programs for fixing tables etc. but I am not sure if they would be of any benefit.

I am ussuming that once I have run the pack and index process once I will be comfortable with it.

Any experts on Foxpro in Vancouver BC?

Question by:ddp1960
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 27

Accepted Solution

CaptainCyril earned 600 total points
ID: 37814154
When you reindex, it would not change anything in the database.

We cannot tell you exactly what to do because we don't know what the application does nor what the programmer had in his mind for empty or deleted records.

PACK removed deleted records from the database.
INDEX creates an index tag on a table
REINDEX uses the same index definitions and recreates the indices but not from scratch.

If you have an empty report, there should be something in the query to say: WHERE NOT EMPTY(somefield)

If you BROWSE the table and see empty records and there are no deleted records then you can delete them:

SET DELETED OFF && Show deleted records in BROWSE
BROWSE FOR EMPTY(id) && if all records are empty then good
BROWSE FOR DELETED() && check if there are records deleted
PACK && This will remove deleted records permanently

Hope this is clear and a step closer to what you are looking for. Please go over it and see how it applies to you and I will be glad to answer any questions you may have.
LVL 42

Assisted Solution

pcelba earned 720 total points
ID: 37814201
Each professional application should offer some Maintenance menu which allows BACKUP, RESTORE, PACK, and REINDEX. All these actions are very simple and also very helpful. Some applications do reindex and pack automatically but that's not so obvious.

If you don't have such option in your app then you may do it manually from Visual FoxPro development environment or you may write a simple program to do it for you.

The backup as the first step is highly recommended. Then you have to ensure the application does not use deleted records as a standard data. It is not obvious but possible. In such case PACKing would damage your data irreversibly.

To reindex and/or pack some table issue following commands:

USE d:\path\YourTable EXCLUSIVE

You don't need to copy tables from the server and back. You just have to ensure the app is not runing during the maintenance. Exclusive file opening would fail if app is running. To create a list of tables you may use ADIR() function and then loop through the array containing the file list:
lnCnt = ADIR(laFiles, "d:\path\*.DBF")
FOR lnI = 1 TO lnCnt
  ? "Indexing " +   laFiles[lnI, 1]
  USE ("d:\path\" + laFiles[lnI, 1]) EXCLUSIVE

Open in new window

You could also use INDEX command instead of REINDEX but INDEX requires to know all index expressions which is not your case probably.

The only FoxPro expert in Canada I know is Steven Black. Unfortunately, not from Vancouver but from the East... and expensive probably :-) http://www.stevenblack.com/contact.html

Programs fixing DBF files do not reindex and pack obviously. You would need them if some of your tables becomes corrupted.

Assisted Solution

rinfo earned 440 total points
ID: 37815655
There are lots of utility free and on purchase that will allow you to manage foxpro (.dbf) files
like http://dbfmanager.com/
Unless indexes are defined for the table . reindexing will not achieve anything.
Look for .ndx and .mdx files among the dbf files. Single index files have .ndx and multiple index files have .mdx extension.If you have them using dbf utility mentioned above or using
reindex in foxpro program will index update the tables.
If reports shows no data on them where you are sure that inventory does have this part maybe
the report is filtered on some attribute that can only be ascertained on looking at the appliction or source code.
I  have been running a company since last 5 years that upgrades forxpro,clipper dos based
application to work in newer version of windows.
For details you may visit www.rahulinfo.com.
And finally 15MB database is not that big.

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

LVL 12

Assisted Solution

jrbbldr earned 240 total points
ID: 37816055
The advice given above is good and would address your Index, Re-Index, and PACK (physically removing records previously marked as Deleted) issues.

However there is a chance that these maintenance routines, while important, may not resolve the issue you report

I have noticed at least once where a report brings up no data for a specific part, however have verified that the part does show on the work ticket.

When you say:  "at least once" - my first question would be...
Is this problem appearing for one or more (some limited quantity) of parts and not appearing for other parts?  
Or is this problem appearing for ALL parts?

If the former, then the issue would appear to be more limited and not likely the result of entire table problems (although table maintenance couldn't hurt).

Without knowing more about how your data is 'architected' within your application, I can only guess that you might very likely have multiple tables containing information regarding your specific parts - using a 'normalized' table architecture.

If so, the part number (field value) itself showing on the work ticket may be coming from one table, where the missing associated additional data may be coming from another table.   If so, the bringing together of the various pieces of information can be accomplished in a couple of ways.

One way the application could be doing this would be through using a VFP RELATION to 'link' the various tables.
A REINDEX of the data tables might resolve this by recreating the Index used to establish this table 'link'.

Another way would be through using a SQL Query to bring together all of the records associated with the part into a separate results record set (cursor, table, etc).    
If this were the situation, then the table maintenance would be less likely to resolve the problem.

One thing to check would be to investigate how your application data is 'architected'.  
If, as I have assumed here, the data for the specific parts is distributed across multiple data tables, the you might want to check to see if the data records in the subsequent tables for a specific part exist and ensure that those records have not been corrupted in some manner.   If they were, then nothing already suggested will resolve the issue until that has been resolved.

As to finding a knowledgeable VFP consultant in Vancouver you might want to do a Google search for     foxpro vancouver

Good Luck
LVL 27

Expert Comment

ID: 37816295
In my application, I have it check for damaged tables and prompt the user to fix the damaged tables or just to reindex.

There is also a checker for each invoice, receipt, order, ..., if the header record matches the child totals.

Author Comment

ID: 37817769
Thanks everyone for the help!
So I have been looking at the report file, it pulls data from 3 tables. I could not see anything wrong or different on the inventory item which did not print. So I reindexed the main table, no effect then I reindexed the next table and yahoo, the report runs properly now.

So, this generates a few more questions in order to finish my problem.

1) should I reindex all of the tables?

2) The old laptop we have FoxPro running on has VPF 7.0 SP1, I would like to run it on a new laptop, however I can find several copies of VFP6 CD's, but haven't found the box of VFP7 cd's yet. So I am stuck running it on the old laptop which I can't set the time on because the user we are using doesn't have rights to set the correct time, and there is a bios password which we can't remember. Well that's another project, cheapest solution is find or buy a new license and load on a new laptop. Should I buy VFP9 ? is there a update process I need to do to our data?

3) So for now I am copying the directory off the server and then coying the files back to the server after reindexed. Can I just copy the dbf file back or do I need to copy the .CDX file also? Any other files?

LVL 27

Expert Comment

ID: 37818521
From my experience, it is best if you reindex right after you copy files from one computer to the other. Also it is healthy to reindex on a periodic basis (week, month, quarter) the whole database.

It seems that there was a computer crash or power failure to cause that index damage.
LVL 42

Assisted Solution

pcelba earned 720 total points
ID: 37818683
The question is WHY did you copy files from the server to reindex them? It is really unnecessary. You should reindex all files at the server. You may either map the server folder to your local computer or you may install FoxPro on the server for this maintenance task.

If you have some company policy to reindex out of the server on a desktop or laptop (I can imagine such policies...) then you shoukld copy the whole data folder (DBF, FPT, CDX, ... simply all files). To copy back just DBFs is a nonsense because indexes are stored in CDXes.

VFP 7, 8, 9 is delivered as a part of MSDN subscriptions (not all levels).

VFP 9 update is available at Amazon: http://www.amazon.com/Microsoft-Visual-Professional-Upgrade-Version/dp/B00066FHMY/ref=pd_cp_sw_2. You shoukd also download SP2 and three available hotfixes from Microsoft.

A big advantage of VFP 9 is the license model - it is licensed per user (you may install it on all your computers but not on computers of your customers) and it also permits to use all previous versions.

You may use VFP 9 for data maintenance tasks (pack, reindex) but to execute your application under VFP 9 will require some source code updates and recompilation so I would not recommend it at the moment.

A cost effective solution exists: You may just download VFP 9 free run-time libraries (http://archive.msdn.microsoft.com/FoxPro/Release/ProjectReleases.aspx?ReleaseId=125) and use the free Visual FoxPro 9 interpreter vRunFox from this page: http://www.leafe.com/dls/vfp

Assisted Solution

rinfo earned 440 total points
ID: 37818774
1. Yes you should definitely reindex all tables.
2. Yes you should consider upgrading to VFP9 . Installing vfp9 will allow you to tools for
    managing database. VFP6 tables are fully compatible with vfp9 and there should not be
    any problem in running vfp 6 application on vfp9.Once vfp 9 is installed you can compile
    and should do that - your vfp 7 application in vfp9.There are some commands to update
    database generated in vfp7 to be fully compatible to vfp 9.
3. You can use vfp installation for reindexing and other database related operations.
4. As i mentioned earlier single table single  index have the extension .mdx and compound
    index have the index extension .cdx. Have a data folder to keep all the tables and index
    so that it will be easy to maintain.
5.there are ways you can recover/reset the bios password.But you better get the service
   technicians who are used to doing this.

Author Comment

ID: 37828991
Thanks for all the help.
So I ran a test offline and it worked fine.
Then  I ran the index on all of the dbf that had related CDX index files from the server.
The report runs properly now.
I was able to reset the Bios password, so that is fixed.
We are going to look at upgrading to VFP 9.
Now I have to allocate points, I will try to be fair, now I realize difficulty with putting too many questions in one post.

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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