Reading DBF Format with VB6 using ADO (howto)

• • Reply

  15 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  This may come easy to most of you, but it threw me for a loop because the DBF format does not have a table - so you use the filename as the table in ADO.  That single piece of information took me awhile to discover.  I did not see a relevant post on it, so thought I would share my code.  If you have any trouble with the below code, feel free to ask questions.
  
  * Note on code, it is a function that returns the quantity of records in the DBF and stores info(i reduced the selection to 4 fields) on the first record of the database to a structured array.  *
  
  Private Function DBFQuantity(FileName As String) As Integer
      Dim conRecordset As ADODB.Connection
      Dim rsRecordSet As ADODB.Recordset
      Dim strSQL As String
      Dim Quantity As Integer
     
      Set conRecordset = New ADODB.Connection
      Set rsRecordSet = New ADODB.Recordset
      'For DBF, the TABLE is the filename.dbf
      strSQL = "SELECT Name, Address, Zip, Endorse FROM " & JobListArray(z).FileName & " ORDER BY PS_Seqno"
      '.DataPath is /path/to/file excluding the filename
      conRecordset.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & JobListArray(z).DataPath & ";Extended Properties=DBase IV"
      rsRecordSet.Open strSQL, conRecordset
     
      'I had to do this manually as .MaxRecords and .RecordCount returned errors
      Quantity = 0
      rsRecordSet.MoveFirst
      Do While Not rsRecordSet.EOF
          rsRecordSet.MoveNext
          Quantity = Quantity + 1
      Loop
     
      rsRecordSet.MoveFirst
      'I skip the first record as this is only headers and not true data
      rsRecordSet.MoveNext
      'Make a do-while loop for NOT rsRecordset.EOF to traverse the complete recordset.
      With JobListArray(z)
          'Record 1
          .RecName1 = rsRecordSet.Fields("Name").Value
          .RecAddress1 = rsRecordSet.Fields("Address").Value
          .RecZip1 = rsRecordSet.Fields("Zip").Value
          .RecEndorse1 = rsRecordSet.Fields("Endorse").Value
      End With
         
      rsRecordSet.Close
      conRecordset.Close
      DBFQuantity = Quantity
  End Function
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by kympax

  dy3ri keykal

  Phils., United States

  Joined 15 years ago

  You can use...

  Private Function DBFQuantity() As Double

    ' Reference : Microsoft ActiveX Data Ojbects 2.0 Library
    ' Save the dbf file in the program's directory first
  

    Dim FXPConn As New ADODB.Connection
    Dim FXPRs As New ADODB.Recordset
    Dim FXPDBSQL As String
    Dim DBFileName As String, PathDBFileName As String
  
  
  

    DBFileName = "sample.dbf"
    PathDBFileName = App.Path
  

    FXPDBSQL = "SELECT * FROM " & DBFileName
    FXPConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
                 "SourceType=DBF;" & _
                 "SourceDB=" & PathDBFileName & ";" & _
                 "Exclusive=No"
                
    FXPRs.CursorLocation = adUseClient ' we will use the RecordCount property
  
  
  
  
  
  

    FXPRs.Open FXPDBSQL, FXPConn, adOpenDynamic, adLockOptimistic

    If FXPRs.EOF Then
      DBFQuantity = 0
    Else
      DBFQuantity = FXPRs.RecordCount
    End If
  
  
  
  

    FXPRs.Close
    FXPConn.Close
  

  End Function

  Or use...

  Private Function DBFQuantity() As Double

    ' Reference : Microsoft Remote Data Object 2.0
   
    Dim FXPConn As New rdoConnection
    Dim FXPRs As rdoResultset
    Dim FXPDBSQL As String
    Dim DBFileName As String, PathDBFileName As String
  
  
  
  
  

    DBFileName = "sample.dbf"
    PathDBFileName = App.Path
  

    FXPConn.Connect = "SourceType=DBF;" & _
                      "SourceDB=" & PathDBFileName & ";" & _
                      "Driver={Microsoft Visual FoxPro Driver}"
  
  

    FXPDBSQL = "SELECT * FROM " & DBFileName
    FXPConn.CursorDriver = rdUseOdbc
    FXPConn.EstablishConnection "rdDriverNoPrompt"
    Set FXPRs = FXPConn.OpenResultset(FXPDBSQL, rdOpenKeyset, rdConcurRowVer)
   
    If FXPRs.EOF Then
      DBFQuantity = 0
    Else
      DBFQuantity = FXPRs.RowCount
    End If
  
  
  
  
  
  
  
  
  

    FXPRs.Close
    FXPConn.Close
  

  End Function

  I usually use the second sample because it can open that database that is already opened by another user.

  Report abuse
• • Reply

  15 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  kympax wrote:

  I usually use the second sample because it can open that database that is already opened by another user.

  
  
  Funny you mention that because I wanted the exact opposite.  I played with RDO and decided against it because of it didn't grab exclusive control(plus I already had ADO elsewhere in the program - adding RDO increased the overhead).  I have a file-lock function that test when the program starts, but I want it to error out if another user tries to access the file while it is running.
  
  I'll make note of the first method for future projects.  For this one, I only needed to grab the quantity and 4 fields of the first 5 records.  The majority of the work is done on SQL and MDB databases - I just link to DBFs to ensure that the proper ones have been selected.
  
  
  
  
  
  

  Report abuse