Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Corrupted FoxPro Indexes in Visual Basic 6

I have a VB 6 Service Pack 5 Application that works with FoxPro 2.6 free tables.  I use DAO 3.5 to open most tables, but on several forms I use the intrinsic data control because I need to display the data in a dbgrid.  I set the databasename, connect, recordsource and recordsettype properties in the form_load event, then in the form_activate event I refresh the data control and assign an index to the recordset.  This form is used for browsing and deleting only (via command buttons).  When I add or edit a record, I open a second form and create an array of dao recordsets.  I set the first recordset to that of the data control from the first form, then I use dao to open up to 2 more recordsets.  When the user clicks on a "Save" button, changes are made to the first recordset, and may or not be made to the second and third recordsets.  If a record locking situation occurs, the edits or addnews are canceled.

This all works fine at home and I never get any problems.  When I install the application at the job site, I almost always get "Object or With Block not set" or "No current Record" errors. As these errors occur, the FoxPro 2.6 indexes become corrupt, and it would be unacceptable to have to reindex all files after each and every addition. I have error logging statements in each and every procedure, function, or event to track date, time, user, machine, error number, error description, routine name, and misc info.

Here are my perceived differences between home and the job site:

1.  Job site computers are much faster than my home machine.  My development machine is a Cyrix Pentium 200 running Windows 98 SE and my server is an Intel Pentium II 350 running Windows 2000 SP2.  The job site has Pentium II 350's and Pentium 4 1600's running either 98, 98 SE, or Windows 2000 SP2, and the server is a Pentium III 800 running Windows 2000.

2.  My home network uses a 5 port LinkSys 10/100 hub that is 2 years old but seldom used.  The Job site has a 3Com 12 Port 10/100 hub that has been REMANUFACTURED and not all of the computers can connect at 100, most connect at 10.  The job site also has a router connecting them to a DSL line, and the connection frequently drops.  Powers that be assure me that although the hub has been remanufactured, it works just fine, and the slower connections are probably attributed to cabling problems.

3.  At this point in time due to very small budgets, it is not possible to switch from a peer-to-peer network to client-server network.

4.  The database files need to remain in FoxPro 2.6 due to Report Writer compatability, plus, if my new system crashes, they need to be able to start up the old system ASAP.

My questions are as follows:

1.  Are these errors due to the legacy database? - One of the reasons for my new application is that the indexes on the tables keep getting corrupted. The second reason for the new application is to increase speed.
2.  If I converted the FoxPro 2.6 tables to FoxPro 3.0 free tables, would this improve my situation?  (I believe the Report Writer works with FoxPro 3.0)
3.  Could the faster machines affect writing changes?  Do I need to put a delay after Editing or Adding via a timer or dbengine statement?
4.  Could the REMANUFACTURED hub, malfunctioning Router, or bad wiring be causing the program to lose the connection with the database or corrupting the indexes? - They frequently get garbage ascii characters in textboxes of memo fields.

Any and all help would be greatly appreciated.
1 Solution
>>When I add or edit a record, I open...etc
i dont quete understand what you are doing...very confusing
vb can not work with foxpro indexes if these indexes are on a foxpro expression
in foxpro you can create indexes on (combinations of) partial fields,or conditional indexes
vb is not able to update these indexes
vb can only work with indexes created on entire fields
problem is if you use a vb compatible index it will work as long as no foxpro program will access the tables
if you add/delete/update records in a foxpro table vb will only update the compatible indexes, and if thereafter the tables are opened with foxpro the other indexes will be out of sync
>>The second reason for the new application is to increase speed.
accessing a foxpro table with VB is magnitudes slower than accessing it with foxpro
however it's been at least 4-5 years since i did anything with foxpro db's and vb (and then it was with vb3 on windows3 )
so dont take my word for it
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

tanderson78Author Commented:
I am aware that VB cannot update foxpro indexes built on expressions.  Other indexes in the old system were built on boolean values, upper case, iif( expressions etc.  In most cases when retreiving small recordsets, an SQL select statement accomplished the same thing.

The speed issue revolves around one large file in particular.  The old system would open the table, join it with a name & address file, and then set a filter on the entire table to return only 1 or a handfull of records.  An SQL statement with an inner join proved faster results than the old method.

Here would be another question then,

If I open a dynaset on a foxpro table with indexes, then edit or addnew, will the table's indexes be updated, or do I need to open it as a table to edit the records I want and then the indexes will be updated properly?
if you use a dynaset-type recordset or a table-type recordset the indexes will be updated/deleted/adnewed if you update/delete/addnew a record
for the best speed use a table-type recordset and set an index to use, that way you can use 'seek', and you will find any record in a million record table immediately
not only that but if you use a dynaset-type the jet engine may load the entire recordset to the local computer,but with a table-type recordset it will only the current record to the local computer, the result of that is, that the user will have the impression that he has immediately access to each and every record in the table
come to think of something: are you using a .inf file to tell the jet engine to use the indexes ?
if not jet will not use the indexes(maybe thats the reason why the indexes become corrupted, they are not, but they are incomplete)
also are the foxpro memo files in the same directory as the tables
but remember its a long time ago i did something with foxpro files, they were foxpro 2.6 files but i used vb3 on windows3,so i can not be sure of this

tanderson78Author Commented:
No,I am not using an .inf file.  I discovered that the existance of an .ini file would make the system use or look for  .idx indexes, whereas I need .cdx indexes.

I do beleive I have solved my problem

   data1.recordset.index = "myindex"

   dim RS as dao.recordset
   set RS = form1.data1.recordset

   set RS = Nothing

I am closing the recordset in form2, and although the dbgrid shows data, the recordset is actually closed, and any repositioning of the data1.recordset triggers the no current record or with block not set

Hi tanderson78,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

tanderson78, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
DanRollins -- EE database cleanup volunteer
Question placed in PAQ

E-E Admin
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now