FIX: Index Created Using ADOX and the Visual FoxPro OLE DB Provider Contains the TRANSFORM Function (314653)

MORE INFORMATION

The Visual FoxPro 7.0 SP1 OLE DB provider uses the TRANSFORM function only when the index expression being created contains more than one column.

Steps to Reproduce Behavior

To reproduce the problem, run the following code. The code uses ADOX to create an index on a character-type column in a Visual FoxPro table, and then displays a Wait window with the index tag expression it created. Note that the TRANSFORM function is used on the single-column expression.

#DEFINE ThePath ADDBS(JUSTPATH(SYS(16)))
CD (ThePath)
CLEAR ALL
RELEASE ALL
CLOSE DATABASES ALL
ERASE MyData_*.*
MakeData()
 
LOCAL loCatalog AS ADOX.CATALOG, ;
 loTable AS ADOX.TABLE, ;
 loIndex AS ADOX.INDEX, ;
 loCols AS ADOX.COLUMNS, ;
 loCacheConn AS ADODB.CONNECTION
 
loCacheConn = NEWOBJECT('ADODB.Connection')
 
WITH loCacheConn
 .ConnectionString = "Provider=VFPOLEDB.1;" + ;
  "Data Source=" + ThePath + "MyData_DBC.dbc;" + ;
  "Collating Sequence=MACHINE"
 .CursorLocation= 3  && adUseClient
 .Mode = 16 && adModeShareDenyNone
 .OPEN
ENDWITH
 
loCatalog = NEWOBJECT('ADOX.Catalog')
loTable = NEWOBJECT('ADOX.Table')
loIndex = NEWOBJECT('ADOX.Index')
 
loCatalog.ActiveConnection = loCacheConn
loTable = loCatalog.TABLES("MyData_DBF")
 
WITH loIndex
 .NAME = "myindex"
 .COLUMNS.APPEND("state")
ENDWITH
 
loTable.INDEXES.APPEND(loIndex)
 
loCacheConn.CLOSE
loCacheConn = NULL
loCatalog = NULL
loTable = NULL
loIndex = NULL
 
USE MyData_DBF
WAIT WINDOW "Index Expression: " + SYS(14,1)
USE
 

*~~~~~~~~~~~~~~~~~~~~~~~~~~
PROCEDURE MakeData
 CLOSE DATA ALL
 CREATE DATABASE 'MyData_DBC.DBC'
 CREATE TABLE 'MyData_DBF.DBF' NAME 'MyData_DBF' (STATE C(2) NOT NULL)
 CLOSE DATA ALL
				

REFERENCES

For additional information about ADOX, refer to the following Knowledge Base article: