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.