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"
*!* 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.


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

Thanks a lot