Copy to &/or Append from appear to miss records

took your advice and it did nothing different still missing records.  I have even tried running thru the database to try and figure which ones are missing and cannot seem to discerene them - it just indicates there is nothing missing

It could be corrupted index or try SET DELETED OFF.

After building the necessary indicies, set up a relation with the original table as the Parent and the COPY result table as the Child.

Then do a Browse on the Parent table for Empty(Child.something) to see the records which did not get copied.
Once you can identify the specific records which did not get copied, then you should be able to identify the problem.

Worst case, you can create a 2nd small table of only those non-copied records and then attempt to append them.

Good Luck

working on that as we speak - there are ore that a million records and I am trying to this programattically
but not having a lot of luck

attching a code snippet as a sort of guide as to what I aha tried

CLEA
CLOSE DATABASES ALL
USE w:\temp\sbt\arinvt01 ALIAS invttemp IN 0   && 1012891 records
USE w:\sbt\arinvt01 ORDER ITEM ALIAS invtnew IN 0  && reuslt table 929399 record
 
=AFIELDS(arfldlist,"invttemp")
CREATE CURSOR sqlMissingINVTParts FROM ARRAY arfldlist
 
? RECCOUNT("invttemp"), RECCOUNT("invtnew")
* above results in 1012891 and 929399
 
SCAN   && invttemp
	@ 10,1 say "invttemp Record: " + STR(RECNO("invttemp"))
	csearch = ALLTRIM(invttemp.item)
 
	IF !SEEK(cSearch,"invtnew")
                  * this never ever trips and I cannot figure why not
		SCATTER NAME missingrec
		INSERT INTO sqlMissingINVTParts FROM NAME missingrec
		@ 12,1 say "Missing Record: " + STR(RECNO("sqlMissingINVTParts"))
	ELSE
		@ 11,1 say "Number Found: " + STR(fndktr)
		fndktr = fndktr + 1
	ENDIF
 
 
	endif
ENDSCAN
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

Keep SET EXACT ON and don't do ALLTRIM()

That does not work either.
Set deleted is off - besides there are no deleted records
Set exact if on and alltrim has been removed.
The program still has no missing records noted. Allthe records in the 100+ million records were found int he table with 930000+ records.  Duplicates are not an issue, there are only six of them otherwise all the item codes are unique. There are no constraints, no triggers, no nothing. One index tag on the file that is being searched [key() = item]

CLEAR
CLOSE DATABASES ALL

USE w:\temp\sbt\arinvt01 ALIAS invttemp IN 0  EXCL    && 1012891 records
USE w:\sbt\arinvt01 ALIAS invtnew IN 0  EXCL   && result table 929399 record

SELECT InvtNew
INDEX ON Item TAG Item  && Re-build Index

SELECT InvtTemp
SET RELATION TO Item INTO InvtNew
COPY TO c:\temp\temp FOR EMPTY(InvtNew.Item)   && Make copy of not-copied records

* --- Analyze Missing Records ---
USE c:\temp\temp IN 0 EXCL
SELECT Temp
INDEX ON Item TAG Item
<Do Whatever>

Good Luck

tried your suggestion and there was nothing in the resulting temp dbf,
now you can see my pain - any ideas will be tried

can you open the original table in vfp and do this:

set talk on
count

and give us the record count please?

Could it be the memo file is damaged? Can you copy the table to another in FPW26 and see if there are missing records?

Assuming that the Relation is set up and working correctly (you should do a BROWSE FOR !EMPTY(item) - just to check), then If there is nothing in the Temp.dbf, then it means that for every Item in the original table, there is a matching record in the new table.

That would imply that the accuracy of RECCOUNT() for the original table might be in doubt - whether due to file corruption or other causes.

Good Luck

jrbbldr: using your technique there is still nothin missing.
what things can cause RECCOUNT to be inaccurate.

If reccount is inaccurate, the file is not opened. Trying to use it give a Not a Database File or Not a Table error. But Reccount includes deleted records.

the file is opened, there is no error at all -

In that case, reccount() is giving the correct result. Either the index is corrupt or you have deleted records.

If you give list structure, you will get the total record length

take the size of the file
subtract 32 from it
subtract 32*n from that. n is the number of fields

divide the number by the record length to get the record count

the size of trhe file from where, DOS, windows or vfp

Type DISPLAY STRUCTURE in the Command Window to get this info.

Total is the size of the record.

DOS, windows and  vfp should give you the same file size.

SET COMPATIBLE on
?FSIZE(YourDbfFileName)

Should give you the file size in VFP.

math you all suggest - works except I still get the same results. ONe table has over a milion records wil  the resulting table after copy or append only has 990,000 plu records

How do you know that there are no deleted records.

It seems to me one of two things:

1) Deleted records exist
2) Corrupted index or table

there are no deleted records according to coutn for deleted() [and yes deleted is set oto off)
the indexes have been rebuilt.

Could there be and differnce caused by the difference between a FPW26 table and VFP 9 table.other than the size of the memo fields [10] in fwp and 4 in vfp]
Note they are both free standign dbf's not dbc.

Did you test the copy on both version of FoxPro?

It could be the original database is corrupt. Have you tried creating a new table with similar structure and append from the original table?

Have not tried that yet. difficult since it is live data and the copy would bring the newtork to a screechingh if done during the day.  Need to wait till the weekend

I think the data table is corrupt since you have set deleted off and rebuilt in indeces.

I handle millions of records in fpw26 and never had problems either on server or on laptop.

problem is still not solved. Have no idea how to find the corruption you all memtion, there has to be a better way than to roll thru each record one my one by hand field by field to find it. Any ideas welcome

CaptainCyril had asked you to do
SET TALK ON
SET DELETED OFF
COUNT

and post the result.

Another thing you can do is

USE InvtNew
INDEX ON Item TAG Item  && Re-build Index
lnDupl=0
lnRec=0
lcLastItem='ZZZZZ'
SCAN
   IF  lcLastItem=Item
      lnDupl=lnDupl+1
   ELSE
      lcLastItem=Item
      lnRec=lnRec+1
   ENDIF
ENDSCAN
?lnRec,lnDupl
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

still wating for a successfull answer - so far unable to figure why the difference in record numbers
have tried the last item sent by captaincyril and the same thing occurs.  Want to start with a fresh copy but have to wait till weekend so I don't drag the whole company's netwrok down at once during the copy.

You can zip the file on the server by remote access and then copy it over the network. This way you don't have to hog the network. At least 90% compression is achievable.

I average 92% on large databases.

good idea, however, in my case for the time being, on database is one server in another location and need bring it to my side.  So far I am not permitted access to writing a file directly to the c drive of the server and then unzip it - but I will check in this asap

by the way I do zip it when I copy the inventory from there to here, it also takes a long toime to zip them up - so still need to weait for non work time