Solved

Corrupted FoxPro Indexes in Visual Basic 6

Posted on 2002-07-16
8
419 Views
Last Modified: 2013-11-25
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.
0
Comment
Question by:tanderson78
8 Comments
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7160456
>>When I add or edit a record, I open...etc
i dont quete understand what you are doing...very confusing
however:
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
 
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7160468
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
 
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7160484
>>4.
yes
0
 

Author Comment

by:tanderson78
ID: 7160745
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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:pierrecampe
ID: 7160911
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

0
 

Author Comment

by:tanderson78
ID: 7161111
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

form1_load
   data1.recordsource="mytable.dbf"
   data1.refresh
   data1.recordset.index = "myindex"

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

form2_unload
   RS.close
   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

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8013044
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
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 8096142
Question placed in PAQ

Computer101
E-E Admin
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

747 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

12 Experts available now in Live!

Get 1:1 Help Now