Creating a CDX file with a new FoxPro Table
David Hahn
I am having a problem with creating a CDX index for a FoxPro DBF file that
I have created with the FoxPro ODBC. The DBF is for a printer cost recovery
program.
I am retrieving data from our accounting database and writing the
appropriate
records to the DBF. I have used Visual FoxPro 6 to determine what indexes
are
required by the program.
I create a new table using the "CREATE TABLE" command , then populate the
table. This works fine. However, when I try to use the "CREATE INDEX"
command, I get a syntax error no matter what I do. I have tried using the
FoxPro native "INDEX ON" command to no avail. Below is some of my code. Is
there a way to tell the driver to create the CDX when the table is created
and subsequently update it when records are added? The reason I ask this
question is that if I open the table after it has been created and NO
records have been added, the byte in the header of the file that indicates
the use of a structural CDX file (byte 28) is not set appropriately.
Dim Conn As ADODB.Connection
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft FoxPro VFP Driver (*.dbf)}; SourceType=DBF;"
& _
"SourceDB=" & Path & ";NULL=No; Exclusive=No;"
dbconn.Execute "Create Table PPOPUP (CODE char(20), SUBCODE char(20), DESCR
char(50), PAGES I(1))"
.
. (populating table using "INSERT INTO")
.
dbconn.Execute "CREATE INDEX T1 ON PPOPUP (code DESC)" 'generate the
index file. <- SYNTAX ERROR HERE
I have tried using the FoxPro INDEX command via ODBC which MSDN says is
supported. I get a
similar error using that. I am beginning to think that the INDEX command
really isn't supported by
the ODBC driver.
In the mean time, I have found a utility provided by the cost recovery
program developers
that will write the DBF and CDX with the proper fields and indexes when I
feed it a properly
formatted CSV file. I am still curious as to why I saw this behavior.
Any assistance you can provide would be greatly appreciated! Let me know if
you need to know anything else. I am using VB6 w/ SP5 and the latest FoxPro
ODBC driver.
Thanks!!!
David Hahn
Cindy Winegarden
David Hahn <david...@microsoft.com> wrote:
> I am having a problem with creating a CDX index for a FoxPro DBF file
>
> I create a new table using the "CREATE TABLE" command , then populate
> the table. This works fine. However, when I try to use the "CREATE
> INDEX" command, I get a syntax error no matter what I do.
The VFP ODBC Help (mine's in C:\Windows\System32\Drvvfp.hlp) does not
include the SQL CREATE INDEX command as part of VFP's SQL installation.
> I have tried using the FoxPro native "INDEX ON" command to no avail.
Not all native FoxPro commands are supported through ODBC.
http://support.microsoft.com/default.aspx?scid=kb;en-us;163031
PRB: Help of VFP ODBC Driver Unclear on INDEX ON Command
"...The INDEX ON command is not supported in the Visual FoxPro ODBC
driver..."
I haven't experimented with OLE DB but the VFP Help says that INDEX is
supported.
> ... Is
> there a way to tell the driver to create the CDX when the table is created
> and subsequently update it when records are added? The reason I ask this
> question is that if I open the table after it has been created and NO
> records have been added, the byte in the header of the file that indicates
> the use of a structural CDX file (byte 28) is not set appropriately....
FoxPro's "structural index" files (CDX) are updated automatically, whether
records are updated via FoxPro or through ODBC or OLE DB. In my test (with
and without an index) byte offset 28 (actually the 29th byte) had 0x01 when
the index was present and 0x00 when it was removed.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.wi...@mvps.org www.cindywinegarden.com
David Hahn
I saw that the "INDEX ON" command is not supported, but it also says the
"INDEX" command is supported in the MSDN I have and the MSDN that is online.
I wish I would have found that link you posted earlier! That's exactly the
conflict I was dealing with. It confused me that they listed the INDEX and
INDEX ON commands separately!
As far at the structural CDX file byte in the file, I see the same behavior
that you see. That works fine. Oh well, I will have to stick with the way I
am doing it now because the database has to have these index's and I don't
want to have to do this manually every month or so.
Thanks again for your help! It is greatly appreciated.
David Hahn
"Cindy Winegarden" <cindy.wi...@mvps.org> wrote in message
news:#x26tfIr...@TK2MSFTNGP11.phx.gbl...