Helloworld2482


I am trying to retrive selected records from foxpro DBF file, i am using VB6 and for

connectivity i am using ODBC. Foxpro DBF file is having more then 4 lacks records

and it is taking lot of time to bring the records.

Can anybody help me to solve this issue,

Regards




Re: Retriving data from DBF file

Alex Feldstein


What does "more than 4 lacks records" mean

Show sample code on what you are doing.

Is the table indexed The record retrieval should be very fast.






Re: Retriving data from DBF file

Helloworld2482

Below the simple code i am using to retrive the data from foxpro table, how we can index the foxpro DBF file from vb6

==============================================================

Set rs = New ADODB.Recordset
rs.Open "Select * from MastNew where plu_Code='" & Trim(txtPluCode) & "' and plu_cpt=" & G_ConceptID, FoxConn,

adOpenForwardOnly, adLockReadOnly

With mfgDataGrid
If Not rs.EOF Then
i = .Rows
.Rows = .Rows + 1

If rs!plu_cpt = 3 And rs!deptCODE = 20 Then

If rs!GRUP_CODE = 56 Or rs!GRUP_CODE = 57 Then
G_ClsFieldID = 2
Else
G_ClsFieldID = 1
End If
Else
G_ClsFieldID = 1
End If

.TextMatrix(i, 0) = IIf(IsNull(rs("DeptCODE")), "", rs!deptCODE)
.TextMatrix(i, 1) = IIf(IsNull(rs("SEASON")), "", Replace(rs!SEASON, "'", ""))
.TextMatrix(i, 2) = Replace(IIf(IsNull(rs("BRAND_NAME")), "", rs!brand_name), "'", "")
.TextMatrix(i, 3) = Replace(IIf(IsNull(rs("SUPPLIER")), "", rs!SUPPLIER), "'", "")
.TextMatrix(i, 4) = IIf(IsNull(rs("PLU_CODE")), "", rs!PLU_CODE)
.TextMatrix(i, 5) = Replace(IIf(IsNull(rs("PLU_DESC")), "", rs!PLU_desc), "'", "")
.TextMatrix(i, 6) = Replace(IIf(IsNull(rs("STYLE")), "", rs!Style), "'", "")
.TextMatrix(i, 7) = IIf(IsNull(rs("GRUP_CODE")), "", rs!GRUP_CODE)
.TextMatrix(i, 8) = IIf(IsNull(rs("SUBGRP")), "", rs!SUBGRP)
.TextMatrix(i, 9) = IIf(IsNull(rs("RETL_PR")), "", rs!RETL_PR)
.TextMatrix(i, 10) = IIf(IsNull(rs("LRCVD_DT")), "", Format(rs!LRCVD_DT, "dd-MMM-yy"))
.TextMatrix(i, 11) = 0 '' Sold qty
.TextMatrix(i, 12) = IIf(IsNull(rs(G_Location)), "", rs.Fields(G_Location))
.TextMatrix(i, 13) = IIf(IsNull(rs("LMW" & G_ClsFieldID)), "", rs.Fields("LMW" & G_ClsFieldID))
.TextMatrix(i, 14) = Val(txtQty)
.TextMatrix(i, 15) = Replace(IIf(IsEmpty(txtRemark), rs!REMARKS, txtRemark), "'", "") '' Remark
.TextMatrix(i, 16) = IIf(IsNull(rs("SDEPT")), "0", rs!SDEPT)
.TextMatrix(i, 17) = Replace(IIf(IsNull(rs("BRND_CD")), "", rs!BRND_CD), "'", "")
.TextMatrix(i, 18) = Replace(IIf(IsNull(rs("SUPL")), "0", rs!SUPL), "'", "")

LblSoldQty = Val(LblSoldQty) + Val(.TextMatrix(i, 11))
LblTotalRequestQty = Val(LblTotalRequestQty) + Val(.TextMatrix(i, 14))
LblTotalItems = Val(LblTotalItems) + 1

.Row = i
.Col = 4 ''' PLU CODE
.CellBackColor = "2459358"
.CellForeColor = vbBlack

.Col = 12 '''LOC SLD
.CellBackColor = "2459358"
.CellForeColor = vbBlack

.Col = 14 '''' REQ QTY
.CellBackColor = "2459358"
.CellForeColor = vbBlack

txtPluCode = ""
txtRemark = ""
txtQty = ""
txtPluCode.SetFocus
Else
MsgBox "Item not found", vbInformation
txtPluCode.SetFocus
Exit Sub
End If
End With

====================================================






Re: Retriving data from DBF file

Naomi Nosonovsky

Hi,

Sorry for referring to another forum, but this problem (indexing from VB application of VFP tables) was discussed in the following thread http://tek-tips.com/viewthread.cfm qid=1356865 on tek-tips forum.

You may get some ideas there.





Re: Retriving data from DBF file

CetinBasoz

Did you try VFPOLEDB driver You can index with execscript() but indexing with VFP would be easier anyway (indexing needs to be done once).

PS: I don't understand what "more than 4 lacks records" mean. How many records are there on the table Is it VFP or your TextMatrix code making it slow Can't you refurnish your SQL to do that for you and directly set datasource to recordset Whenever I used datagrid, MSHFlexgrid and alike it was the grid which is slow especially when done 'cell by cell'.





Re: Retriving data from DBF file

Helloworld2482

i am using foxpro2.6 and more then 4 lacks records means the amount of data is big thats way it is taking time.





Re: Retriving data from DBF file

CetinBasoz

So did you try VFPOLEDB or not



Re: Retriving data from DBF file

Naomi Nosonovsky

Do you have Foxpro for DOS somewhere installed If yes, then you may need to add indexes directly in FoxPro as I think the discussion suggested would not address your problem.

Also is there a possibility to upsize your database to different back-end, such as SQL Server





Re: Retriving data from DBF file

Helloworld2482

I didn't try vf, and the table is already index in foxpro but if we try to retrive from vb6 it taking lots of time.

I think i need to do index from vb6, but we do index from vb6





Re: Retriving data from DBF file

CetinBasoz

If the table is indexed then you don't have to do something with indexing. I don't understand what do you mean in your 2nd sentence.

Try VFPOLEDB. When saying slow I still suspect the slow part is not foxpro itself but your code. You said your data was big, what does it mean How big it is How many records File size

Did you time just getting the data w/o any datagrid processing How long does it take

Did you try getting the same data from with foxpro How long does it take

Does it run faster if you put the same data on SQL server and do this from there