none
Retriving data from DBF file RRS feed

  • Question

  • 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

    Wednesday, June 13, 2007 1:27 PM

Answers

  • 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?

    Thursday, June 14, 2007 10:27 AM

All replies

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

    Wednesday, June 13, 2007 1:35 PM
  •  

    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

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

     

     

    Wednesday, June 13, 2007 1:49 PM
  • 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.

    Wednesday, June 13, 2007 2:12 PM
    Moderator
  • 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'.

    Wednesday, June 13, 2007 2:47 PM
  •  

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

    Wednesday, June 13, 2007 2:51 PM
  • So did you try VFPOLEDB or not?
    Wednesday, June 13, 2007 3:02 PM
  • 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?

    Wednesday, June 13, 2007 3:02 PM
    Moderator
  •  

    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

    Thursday, June 14, 2007 6:33 AM
  • 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?

    Thursday, June 14, 2007 10:27 AM