Sergio Aguilar


Hello, I'm trying to create a table of dbase with a cdx index, but when I create the index, the type of it is MDX, so I want to know if there is a way to create the dbase file with a cdx index in vb.net, tnx for your help


Re: How to create a dBase III Table with a CDX index

Cindy Winegarden


Hi Sergio,

Are you trying to create a dBase III table or a FoxPro DBF Can you post the code you are using and tell us which ODBC driver you are using







Re: How to create a dBase III Table with a CDX index

Sergio Aguilar

The code that I'm using is the one above, What I'm tying to create is a DBF file that is a Dbase III file with an CDX index, but I cant generate CDX index files if I use another type of provider, for example if I use the Microsoft Jet OLEDB 4.0 and set the extended properties to dBASE III when I try to generate the index I get an MDX index file, tnx for your help

'This function sets the connection

Public Sub setconexion(ByVal ubicacion As String)
If Not objCnx Is Nothing Then
If objCnx.State = ConnectionState.Open Then
objCnx.Close()
End If
End If
objCnx.ConnectionString = Nothing
objCmnd.Connection = Nothing
strcnx = " Provider=vfpoledb.1;Data Source=" + ubicacion + ";Collating Sequence=machine "
objCnx.ConnectionString = strcnx
objCmnd.Connection = objCnx
End Sub

'This function creates the DBF table

Public Function CrearArchivo(ByVal ubicacion As String, ByVal nombre As String, ByVal campos As String, ByVal tipos As String, ByVal tamanos As String) As Boolean
Dim exst As Boolean
Dim strcmps As String()
Dim strtps As String()
Dim strtmns As String()
Dim icmps As Integer
Dim itps As Integer
Dim itmn As Integer
Dim strcmnd As String
Dim straux As String
Dim i As Integer
exst = Directory.Exists(ubicacion)
If exst Then
exst = File.Exists(ubicacion + nombre + ".dbf")
If Not exst Then
setconexion(ubicacion)
strcmps = Split(campos, ",")
strtps = Split(tipos, ",")
strtmns = Split(tamanos, ",")
icmps = UBound(strcmps)
itps = UBound(strtps)
itmn = UBound(strtmns)
If (icmps = itps) And (icmps = itmn) Then
strcmnd = "CREATE TABLE " + nombre
For i = 0 To icmps
straux = straux + strcmps(i) + " " + strtps(i) + " " + strtmns(i) + ","
Next
straux = "(" + straux.Substring(0, Len(straux) - 1) + ")"
strcmnd = strcmnd + straux
objCnx.Open()
objCmnd.CommandText = strcmnd
objCmnd.ExecuteNonQuery()
objCnx.Close()
objCmnd.Connection = Nothing
Else
CrearArchivo = False
End If
Else
CrearArchivo = False
End If
Else
CrearArchivo = False
End If
End Function

'This function add's an index

Public Function agregarindice(ByVal ubicacion As String, ByVal nombre As String, ByVal campo As String, ByVal nombretag As String) As Boolean
Dim i As Integer
Try
setconexion(ubicacion)
objCmnd.CommandText = "EXECSCRIPT(" & "'SELECT 0' + chr(13)+chr(10)+" & _
"'USE [" + ubicacion + "\" + nombre + "] IN 0 EXCLUSIVE'+ chr(13)+chr(10)+" & _
"'INDEX ON " + campo + " TAG IDX" + nombretag + "')"
objCnx.Open()
i = objCmnd.ExecuteNonQuery()
objCnx.Close()
objCmnd.Connection = Nothing
If i > 0 Then
agregarindice = True
Else
agregarindice = False
End If
Catch ex As Exception
agregarindice = False
End Try
agregarindice = True
End Function






Re: How to create a dBase III Table with a CDX index

Cindy Winegarden

Sergio Aguilar wrote:

strcnx = " Provider=vfpoledb.1;Data Source=" + ubicacion + ";Collating Sequence=machine " ......

objCmnd.CommandText = "EXECSCRIPT(" & "'SELECT 0' + chr(13)+chr(10)+" & _
"'USE [" + ubicacion + "\" + nombre + "] IN 0 EXCLUSIVE'+ chr(13)+chr(10)+" & _
"'INDEX ON " + campo + " TAG IDX" + nombretag + "')" .....

Hi Sergio,

I see you're using VFP OLE DB. That's good except that it doesn't create dBase III formatted tables but rather VFP tables. They are not natively readable with applications such as Excel 2003.

Your FoxPro code isn't quite right. "Select 0" selects the next open work area but "Use ... In 0" opens a table in the next open work area without leaving that work area as the currently active one. In any case, the when issued via OLE DB, the Use command will work just fine if you're only working with one table.

The following code creates a CDX index. Please try it and see what you get:

Code Snippet

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=D:\Temp\;")
cn1.Open()

Dim cmd1 As New OleDbCommand("Create Table TestMDX (Field1 I, Field2 C(10))", cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand("ExecScript(" & _
"[Use TestMDX Exclusive] + CHR(13) + CHR(10) + " & _
"[Index On Field1 Tag Field1 ] + CHR(13) + CHR(10))", cn1)
cmd2.ExecuteNonQuery






Re: How to create a dBase III Table with a CDX index

Sergio Aguilar

The code that you sended works just fine, but it still create a VFP table, you wroted that the VFP OLE DB doesnĄ¯t create dbase III files, what type of connection can I use to create the dBase III Files with CDX indexes tnx for your help



Re: How to create a dBase III Table with a CDX index

Cindy Winegarden

Hi Sergio,

I've never used MDX indexes but from huntng around a little it seems that MDX files are created with dBASE IV or Borland InterBase. You would need an ODBC driver or OLE DB data provider specific to those applications.

Also, ExecScript may or may not work with either of those databases.






Re: How to create a dBase III Table with a CDX index

Carl Warner

dBASE III never had support for MDX files. That old last century db system supported ndx files.
I used to use it before stumbling upon the much faster FoxBASE+.




Re: How to create a dBase III Table with a CDX index

dni

After you create table (with CREATE command) filled with data, you may use:

Code Snippet

COPY TO db3Table TYPE FOXPLUS

USE db3Table shared

INDEX ON YourField TAG YourTag OF "db3Table.cdx" ASCE ADDI

I think Type FOXPLUS is recognized by db3.