>Hi,
>
>Thanks a lot for your response.
>>First, there are serious problems in how you're constructiong your index command; I'd try the following modifications:
>>
>>

lcCommand=;
>>   "INDEX ON " + AllTrim(IndxExpr) + ;
>>   " TAG " + Alltrim(TagName) + ;
>>   IIF(Not Empty(Filter), " FOR "+Alltrim(Filter), " ") + ;
>>   + ;
>>   IIF(UPPER(Type)="CANDIDATE"," CANDIDATE","") + ;
>>   IIF(TAGCOUNT(lcTableName) > 0, " ADDITIVE", "")

>
>
> Thanks a lot. I changed it to this and now it works just fine. I have some problems with content of my IndxDict, but it's another issue.
>
> Actually, how can we check on the fly if IndexExpression is valid for this particular table?
>
>>Second, you haven't allowed for UNIQUE indexes (I don't use them in permanent tags myself, but you need to check if you are supporting them.
>
> Yes, you're right. I can add another IIF statement, but in our DBC we don't use UNIQUE indexes, so I get rid off it right now. I can change this lcCommand later...
>
>>Third, in order to get the most benefit from recreating all indexes, you should issue a DELETE TAG ALL, so that you make certain to recover any disk space from index bloat in the current .CDX file. Doing this has some serious .DBC ramifications that may require you to rebuild some of the information in your database container.
>
> Ok, did it. Actually, before I create my DataBase I deleted all files in DataBase directory, so this command is unnecessary, but I put it anyway.
>
>>
>>You need to make certain that the file has been opened exclusively before doing any of this.
>
> Right. But they open exclusively by default as I found.
> These are my steps:
> 1) Create Database (dbName)
> 2) Create (TableName) DataBase (dbName) FROM ...
> 3) Create Indexes
>
> This is my code (first version, can contain some errors)
>
>*!* Generate State.DBC from "scratch"

Nadya, you could simplify your life a great deal. There's a utility included with VFP called GENDBC that will take a .DBC and generate all the code needed to construct it from scratch. If what you're trying to do is create everything from a standing start, run it against your .DBC and use the code it generates. As I remember, there is a significantly enhanced version available for download in the Files section here on UT as well!

>*!* Check the existence of State.DBC
>Local lcStatePath, lcStateDBC, lcStateFiles, lcSafety, lnResponse
>*!* Hardcoded path - check this
>lcStatePath="M:\REDP\DBC\StateTest\"
>lcStateDBC=lcStatePath+"StateTest.DBC"
>lcStateFiles=lcStatePath+"*.*"
>lcSafety=SET('Safety')
>IF lcSafety="ON"
> SET SAFETY OFF
>ENDIF
>
>IF File(lcStateDBC) && check existence of this file
> IF DBUSED(lcStateDBC) && Database is open, can not proceed
> =MessageBox ("File "+lcStateDBC +" is open now. Can not proceed!", 48, "Warning")
> return .f.
> ELSE
> lnResponse=;
> MessageBox ("All files in "+lcStatePath +" will be deleted now! Are you sure?", 4+48, "Warning")
> if lnResponse=6 && Yes
> ERASE (lcStateFiles) && Delete files
> else
> return .f.
> endif
> ENDIF
>ENDIF
>
>wait window nowait "Wait while State.DBC generating..."
>SELECT DataDict
>Local laTableNames
>Local lnTagCount, lni, lnk, lcOrder, lcTableName,;
> lcTempPath, lcTempTable, lcTempSelect, lcTempFiles, ;
> lcFieldName, lcComment, lcCaption,lcNextIDTable
>lnTagCount=TAGCOUNT()
>
>lcTempPath="c:\windows\temp\" && Temp path hardcoded
>lcTempTable=lcTempPath+"tempror.dbf" && Temp table hardcoded
>lcTempFiles=lcTempPath+"tempror.*" && Temp files hardcoded
>
>lcOrder=Order() && Save the current order
>
>DIMENSION laTableNames[lnTagCount-2]
>*!* Hardcoded TableNames
>lnk=1
>FOR lni=1 to lnTagCount
> if UPPER(TAG(lni))="FIELD_NAME" OR ;
> UPPER(TAG(lni))="BLDMSTR"
> ** Skip it
> else
> laTableNames[lnk]=TAG(lni)
> lnk=lnk+1
> endif
>ENDFOR
>
>CREATE DATABASE (lcStateDBC) && Create State.DBC, database open automatically
>lnk=0
>FOR lni=1 TO ALEN(laTableNames,1)
> lcTableName=Proper(laTableNames[lni])
> SELECT * FROM DataDict WHERE &lcTableName>0 ;
> INTO DBF (lcTempTable) && Select only set of records
>
> IF _TALLY>0 && To be sure
> lnk=lnk+1
> lcTempSelect=Alias()
>
> INDEX ON (lcTableName) TAG (lcTableName)
> GO TOP
> Replace Table_Name with LOWER((lcTableName))
> create (lcStatePath+lcTableName) DATABASE (lcStateDBC);
> from (lcTempTable) && Creates table within DBC (AssrMstr, PropMstr, etc.)
>
> IF NOT USED(lcTempTable)
> USE (lcTempTable) Alias (lcTempSelect) in 0
> ENDIF
> SELECT (lcTempSelect) && Select Temprorary table
>
> SCAN
> lcFieldName=Lower(lcTableName)+'.'+lower(alltrim(field_name))
> lcComment=Left(Alltrim(Definition),255)
> lcCaption=Alltrim(Header)
> DBSETPROP(lcFieldName, 'field', 'comment', lcComment)
> DBSETPROP(lcFieldName, 'field', 'caption', lcCaption)
> ENDSCAN
> USE IN (lcTempSelect)
> ERASE (lcTempFiles)
> *!* Create indexes
>
> SELECT (lcTableName) && Assume that this table is open exclusive
> DELETE TAG ALL
>
> SELECT IndxDict
> SCAN FOR UPPER(Table)=Upper(lcTableName)
>
> lcCommand=;
> "INDEX ON " + AllTrim(IndxExpr) + ;
> " TAG " + Alltrim(TagName) + ;
> IIF(Not Empty(Filter), " FOR "+Alltrim(Filter), "") + ;
> IIF(UPPER(Type)="CANDIDATE"," CANDIDATE","") + ;
> IIF(TAGCOUNT(lcTableName) > 0, " ADDITIVE", "")
>
> SELECT (lcTableName)
> &lcCommand
> SELECT IndxDict
> ENDSCAN
> ENDIF
>
>ENDFOR
>
>** Add NextID table
>lcNextIDTable=lcStatePath+"NextID"
>CREATE TABLE (lcNextIDTable)(Table C(8), ID I)
>Alter Table (lcNextIDTable) Alter Column Table;
>SET CHECK !Empty(Table) ERROR "Table should have a value"
>Alter Table (lcNextIDTable) Alter Column ID;
>SET CHECK !Empty(ID) ERROR "ID should have a value"
>Index on UPPER(table) TAG TABLE CANDIDATE
>CLOSE DATABASE && Close State.DBC
>WAIT CLEAR
>=MessageBox(Alltrim(str(lnk+1))+" tables were created",0,"Information")
>SET SAFETY &lcSafety
>SELECT DataDict
>IF NOT Empty(lcOrder)
> SET ORDER TO (lcOrder)
>ENDIF
>
>*******************************************
>
>I also would issue a USE and FLUSH afterwards to make certain that the updated .CDX file is forced out to disk completely.
>
> Could you please explain this, because I haven't use these commands before.
>

VFP keeps some portion of the data that it works with in memory, so that if a subsequent reference is made to the file segment(s) in memory, it doesn't have to wait on the disk to service the request. Unfortunately, this includes a delayed write strategy that may not force all the data out to disk immediately. Closing the table by issuing a USE to close it, and then issuing a FLUSH to write VFP's dirty (changed) data buffers to disk, minimize the chances of a cow flying by before the data is safe on the hard drive.

You still aren't completely protected (Windows, and on a network, your network server, may have delayed write strategies of their own) but this at least makes VFP request that the operating system try to write its updates out to disk before starting the next thing to do.

>
>>I made the decision to use SDT a couple of years back rather than rolling my own table, index and .DBC maintenance; it's an amazingly powerful utility that can be incorporated into your products and distributed royalty-free once you buy it. I couldn't come close to duplicating the functionality or reliability of SDT in a reasonable amount of time; Doug Hennig has done a great job of supporting his product and enhancing SDT to date. It's supported by most of the major frameworks on the market (in my case, since I use a framework built around Visual MaxFrame Pro, all the necessary hooks are right there in the framework), and uses the DBCX2 standard to provide extensibility to the .DBC that has very wide product support as well.
>
> Actually, I beleive, I can achive our goals using only our code (because I already spent a lot ot time on this project) and your kindnest support.
>

OK, but I'd point out that I'd spent lots of time writing code to do many of the things that SDT does, too, and being lazy, and not having all the functionality that SDT encompassed, made it clear that abandoning my own continued attempts to do what Doug's product already did and buying SDT would be less costly than continuing to try to write my own, especially when you consider the cost of doing things wrong - a well-tested, well-debugged piece of code has a value well in excess of the time to write the first cut, and it was clear to me that (cost of the time spent previously) + (cost of SDT) was an order of magnitude less than (cost of the time spent previously) + (cost of firther development) + (cost of fixing things I hadn't thought of already in SDT)!

> Thanks a lot