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.

- Read More...

Export SQL data into Text File

Notepad is one good example of a text file. This method will show you how to export your data from your SQL database into your text file using VB6.0.

The RunTextFileReport function is an accounting application that will pass a date "datef " of transaction, and generate a report that will be stored in notepad.

Function RunTextFileReport(string datef)
Dim rs2 As New ADODB.Recordset
Dim sql1, pathz, s As String
Dim filename As String
Dim sDBPath As String
Dim temp As Double
Dim fso As New FileSystemObject
Dim thefile As TextStream
Dim des As String

On Error GoTo Err_Handler

'connecting to your database
Set rs2 = New ADODB.Recordset
sql1 = "SELECT * FROM SALES WHERE TSL_DTE = '" & datef & "'"

rs2.Open sql1, cConn, adOpenForwardOnly, adLockReadOnly

'create a textfile filename using date format 
filename = Format(datef, "mmddyy")
'create a textfile filepath
pathz = App.Path & "\Report\" & filename & ".txt"

'create a textfile.
'pathz is the filepath and true(means you can overwrite the existing file)
Set thefile = fso.CreateTextFile(pathz, True)

' dont be confuse about the format , you can get raid of it.
' this program is required to have a 258 character in the textfile .
Do While rs2.EOF = False
 thefile.Write (rs2!BR_CODE & " " & rs2!N_CHECK & " " & _
         Format(rs2!CLAS_C, "00") & " " & Format(rs2!CLAS_TRD_C, "000") & " " & _
         Format(rs2!STOR_NO, "00") & " " & Format(rs2!tsl_NEW_A, "000000000.00") & " " & _
         Format(rs2!TSL_OLD_A, "000000000.00") & " " & Format(rs2!TSL_DAY_A, "000000000.00") & " " & _
         Format(rs2!TSL_DIS_A, "00000.00") & " " & _
         Format(rs2!TSL_TAX_A, "00000.00") & " " & _
         Format(rs2!TSL_ADJ_A, "0000000.00") & " " & _
         Format(rs2!TSL_NET_A, "000000000.00") & " " & _
         Format(rs2!TSL_VOID, "000000000.00") & " " & _
         Format(rs2!TSL_RFND, "000000000.00") & " " & _
         Format(rs2!TSL_TX_SAL, "000000000.00") & " " & _
         Format(rs2!TSL_NX_SAL, "000000000.00") & " " & _
         Format(rs2!TSL_CHG, "000000000.00") & " " & _
         Format(rs2!TSL_CSH, "000000000.00") & " " & _
         Format(rs2!TSL_ZCNT, "0000") & " " & Format(rs2!TSL_DTE, "mm/dd/yy"))
rs2.MoveNext
If rs2.EOF = True Then
 Set rs2 = Nothing
    ' this will create a backup file to the other path which i think is very important for an application like this
des = "C:\backup\" & filename & ".txt"
fso.CopyFile pathz, des, True
s = "Notepad" & " " & pathz
   'Shell is a predefine function that will automatically open your textfile after exec
Call Shell(s, vbMaximizedFocus)
Exit Function
End If
Loop
Err_Handler:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
End Function

You can do Open (filepath) and Input (fields) for retrieving the data inside the text file. Read More...

Export SQL data into Excel Spreadsheet

The simplest way in exporting your SQL database to excel is using the Enterprise Manager SQL 2000

This is a simple accounting application. This will show you how to export your application to Excel using VB6.0

Let us assume that you are now connected to your SQL database. and all you need to do is to get your data information from your SQL and put it in Excel app.



And be sure you already added a reference in your VB6 program to access the Excel App. (refer the pic. above).

On Error GoTo Err_Handler
Dim rs2 As New ADODB.Recordset
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim nrow As Integer

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Get a new workbook.
Set oWB = oXL.Workbooks.Add
Set oSheet = oWB.ActiveSheet

' Add table headers going cell by cell.
With oSheet
.Cells(1, 1).Value = "BR_CODE"
.Cells(1, 2).Value = "N_CHECK"
.Cells(1, 3).Value = "CLAS_C"
.Cells(1, 4).Value = "CLAS_TRD_C"
.Cells(1, 5).Value = "STOR_NO"
.Cells(1, 6).Value = "TSL_NEW_A"
End With

' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "AA1")
.Font.Bold = True
.VerticalAlignment = xlVAlignCenter
End With

Here's some of the format that you can apply in your fields since it wont automatically change the data type you have in your SQL. Don't be confuse about the cell row since my existing program has many fields..you can have your way. But more or less it looks like these.

' Fill date apply format.
 Set oRng = oSheet.Range(oSheet.Cells(3, 27), oSheet.Cells(nrow, 27))
     oRng.NumberFormat = "mm/dd/yy"
' Numeric apply format.
oSheet.Range(oSheet.Cells(3, 9), oSheet.Cells(nrow, 10)).NumberFormat = "00000.00"
oSheet.Range(oSheet.Cells(3, 6), oSheet.Cells(nrow, 8)).NumberFormat = "0000"

and have this at end of the code

 oXL.Visible = True
 oXL.UserControl = True

' Cleanup all objects  (resume next on errors)
Set rs2 = Nothing
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

  Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number

Excel is indeed a great product and will be very useful to our users for the indefinite future.
User can easily manage excel application for reporting purposes.

Read More...