Export SQL Data Into .DBF (foxpro)

This will show you how to export your SQL data into a .DBF file which is a Foxpro application.
The mean purpose on having this is to get all your data from the SQL database and generate a report that can be restore in the Foxpro database.
This is an accounting sample program using VB6.0.

Dim rsql As New ADODB.Recordset  'sql recordset 
   Dim sqlz As String               'sql statement
   Dim ddate As String              'date
   Dim pathz As String              'path

 On Error GoTo Error_Handle

 'SQL connection
 Set rsql = New ADODB.Recordset

 'Create a connection to SQL.
 sqlz = "SELECT * FROM SALES WHERE TRANDATE BETWEEN '" & datef & "' AND '" & datet & "'"

      rsql.Open sqlz, cConn, adOpenForwardOnly, adLockReadOnly
 
      Do While rsql.EOF = False
      Set myTDef = New TableDef   'set an object for table definition
      Set myRS = New Recordset    ' recordset
    
      'create a file name date Formated
      ddate = Format(Month(rsql!TRANDATE), "00") & Format(Day(rsql!TRANDATE), "00")
       'create a file path
      pathz = App.Path & "\Report\"

       'create and set a table for foxpro database
      Set mydb = OpenDatabase(pathz, False, 0, "DBase 5.0;")
      Set myTDef = mydb.CreateTableDef(ddate & ".dbf")
     'create fields name and its datatype and size

 With myTDef
           'dont be confuse on the data field name i had
                                     'fieldname, datatype, digit no.
          .Fields.Append .CreateField("TRANDATE", dbDate, 8)
          .Fields.Append .CreateField("OLDGT", dbCurrency, 6)
          .Fields.Append .CreateField("NEWGT", dbCurrency, 6)
          .Fields.Append .CreateField("DLYSALE", dbCurrency, 6)
          .Fields.Append .CreateField("TOTDISC", dbCurrency, 6)
          .Fields.Append .CreateField("TOTREF", dbCurrency, 6)
          .Fields.Append .CreateField("TOTCAN", dbCurrency, 6)
          .Fields.Append .CreateField("VAT", dbCurrency, 6)
          .Fields.Append .CreateField("TENTNAME", dbText, 10)
          .Fields.Append .CreateField("BEGINV", dbText, 6)
          .Fields.Append .CreateField("ENDINV", dbText, 6)
          .Fields.Append .CreateField("BEGOR", dbText, 6)
          .Fields.Append .CreateField("ENDOR", dbText, 6)
          .Fields.Append .CreateField("TRANCNT", dbInteger, 9)
          .Fields.Append .CreateField("LOCALTX", dbCurrency, 6)
          .Fields.Append .CreateField("SERVCHARGE", dbCurrency, 6)
          .Fields.Append .CreateField("NOTAXSALE", dbCurrency, 6)
          .Fields.Append .CreateField("RAWGROSS", dbCurrency, 6)
          .Fields.Append .CreateField("DLYLOCTAX", dbCurrency, 6)
          .Fields.Append .CreateField("OTHERS", dbCurrency, 6)
          .Fields.Append .CreateField("TERMNUM", dbText, 3)
      End With
      ' append it to the table
      mydb.TableDefs.Append myTDef

      ' open the foxpro table 
      Set sql = mydb.CreateQueryDef("")
      sql.sql = "select * from " & ddate & ".dbf;"
      Set myRS = sql.OpenRecordset()
    
   ' assigning the value you have in the SQL.
        With myRS
          .AddNew
              !TRANDATE = rsql!TRANDATE
              !OLDGT = rsql!OLD_GT
              !NEWGT = rsql!NEW_GT
              !DLYSALE = rsql!DLYSALE
              !TOTDISC = rsql!TOTDISC
              !TOTREF = rsql!TOTREF
              !TOTCAN = rsql!TOTCAN
              !VAT = rsql!VAT
              !TENTNAME = rsql!TENTNAME
              !BEGINV = rsql!BEGINV
              !ENDINV = rsql!ENDINV
              !BEGOR = rsql!BEGOR
              !ENDOR = rsql!ENDOR
              !TRANCNT = rsql!TRANCNT
              !LOCALTX = rsql!LOCALTX
              !SERVCHARGE = rsql!SERVCHARGE
              !NOTAXSALE = rsql!NONTAXSALE
              !RAWGROSS = rsql!RAWGROSS
              !DLYLOCTAX = rsql!DLYLOCTAX
              !OTHERS = rsql!OTHERS
              !TERMNUM = rsql!TERMNUM
          .Update
      End With
              ' Cleanup all objects  (resume next on errors)
            Set mydb = Nothing
            Set myTDef = Nothing
            Set myRS = Nothing
            Set sql = Nothing
     rsql.MoveNext
          If rsql.EOF = True Then
            MsgBox " Data(s) finish loaded! ", vbInformation, "Report Form"
            Set rsql = Nothing
            Exit Function
          End If
    Loop
Error_Handlr:
     MsgBox Err.Description, vbCritical, "Error: " & Err.Number

But the simplest way you can do is to go to your SQL Enterprise Manager and do the exporting, but it does not create any report.

You can post a comment if you have questions and for further explanation.

-