I have been trying to find a way of properly committing/protecting data to tables in the event of a power fail.

I have a test program which opens 7 tables with tablebuffering, in a database in VFP6.0. The code then:-
************************************************************************
FOR i = 1 To 1200 &&add records into the table
BEGIN TRANSACTION
SELECT transfers &© records from szdtfrhd
SCATTER MEMVAR MEMO
SKIP
INSERT INTO szdtfrhd FROM MEMVAR
INSERT INTO szdtfrh1 FROM MEMVAR
INSERT INTO szdtfrh2 FROM MEMVAR
INSERT INTO szdtfrh3 FROM MEMVAR
INSERT INTO szdtfrh4 FROM MEMVAR
INSERT INTO szdtfrh5 FROM MEMVAR
INSERT INTO szdtfrh6 FROM MEMVAR
DO Flush_Buffers WITH 'szdtfrhd'
DO Flush_Buffers WITH 'szdtfrh1'
DO Flush_Buffers WITH 'szdtfrh2'
DO Flush_Buffers WITH 'szdtfrh3'
DO Flush_Buffers WITH 'szdtfrh4'
DO Flush_Buffers WITH 'szdtfrh5'
DO Flush_Buffers WITH 'szdtfrh6'
@1,10 SAY "Added Record " + STR(i, 4, 0)
END TRANSACTION &&Now commit everything to the database
FLUSH
NEXT i

***********************
PROCEDURE Flush_Buffers
***********************
PARAMETERS cAlias
*
*Commits the buffered table supplied and closes it
*
IF USED(cAlias)
SELECT (cAlias)
IF CURSORGETPROP('Buffering') = 5 &&Must be optimistic table buffering
=TABLEUPDATE(1, .T.)
ENDIF
FLUSH
ENDIF
RETURN
************************************************************************

After this loop has run for say 1200 records I turn off the power. On restart Scandisk detects problems with the tables and fixes them. Once back into VFP I need to reindex the tables to use them. However, the number of records in each table is far short of the 1200, and is different across the 7 tables.

I've also tried the same thing using 2 Win2000 systems with a Windows network, writing into a datbase/tables on one of the systems. I allow both to run, adding to the tables, then switch off the "server" machine.

After the server had added 201, and the client 433, the server was powered off.

The number of records in the various tables were:-
1. 1,106
2. 1,106
3. 1,043
4. 1,106
5. 1,088
6. 917
7. 998

The number of records should have been 1,136, since I started with 502 in each table.

Now I wouldn't mind losing 1 or 2 records, but to lose several hundred AND for there to be different numbers in each table is bizarre. I thought FLUSH was meant to get everything out to the disk?

I've also tried actually closing the database on each iteration and I still lose records. So I can only think that Windows is not performing a proper flush but is caching the command, until it feels like it. I also tried putting an INKEY(1) in the loop to give Windows time to Flush, but it didnt help.

I found an article in the Microsoft Knowledgebase describing a bug in Visual C++, V6.0 and below, where fflush() and flushall() dont write directly to the disk, and it suggests using the FlushFileBuffers() API call. But this in turn needs to be given a file handle. How do I get the file handle of the VFP Database/Table/Index/Memo files?

Any ideas would be gratefully recieved

Kind Regards

Malcolm Sheldon