Solved

Foxpro VP 6 Packing

Posted on 2012-04-05
10
767 Views
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?

Thanks
Doug
0
Comment
Question by:ddp1960
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 150 total points
Comment Utility
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.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 180 total points
Comment Utility
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
REINDEX
PACK
USE

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
  REINDEX
  USE
NEXT

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.
0
 
LVL 9

Assisted Solution

by:rinfo
rinfo earned 110 total points
Comment Utility
There are lots of utility free and on purchase that will allow you to manage foxpro (.dbf) files
properly.
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.
0
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 60 total points
Comment Utility
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
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:ddp1960
Comment Utility
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?

Thanks
Doug
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
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.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 180 total points
Comment Utility
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
0
 
LVL 9

Assisted Solution

by:rinfo
rinfo earned 110 total points
Comment Utility
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.
0
 

Author Comment

by:ddp1960
Comment Utility
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.
Thanks
Doug
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

16 Experts available now in Live!

Get 1:1 Help Now