Exporting Data from SQL Server

You'll need to have a Reference set to the Microsoft ActiveX Data Objects Library.  This will show you how to format the SQL as well as actually print the file...


Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

Private Sub Command1_Click()
Dim sSQL As String
Dim j As Long
Dim fHandle As Long
Dim sTemp As String
Dim sSepValue As String

'    sSepValue = ","
    sSepValue = vbTab
   

    sSQL = "SELECT * FROM user_base WHERE USER_NO = USER_NO"
    If txtSSN.Text <> "" Then
        sSQL = sSQL & " AND USER_SSN = '" & txtSSN.Text & "'"
    End If
    If txtFirst.Text <> "" Then
        sSQL = sSQL & " AND USER_FIRSTNAME = '" & txtFirst.Text & "'"
    End If
    If txtMiddle.Text <> "" Then
        sSQL = sSQL & " AND USER_MIDDLENAME = '" & txtMiddle.Text & "'"
    End If
    If txtLast.Text <> "" Then
        sSQL = sSQL & " AND USER_LASTNAME = '" & txtLast.Text & "'"
    End If

    Set RS = New ADODB.Recordset
    RS.LockType = adLockBatchOptimistic
    RS.CursorType = adOpenStatic
    RS.ActiveConnection = CN
    RS.Source = sSQL
    RS.Open
    If Not RS.State = adStateOpen Then
        MsgBox "Recordset Open Failed"
        Exit Sub
    End If

    fHandle = FreeFile
   
    Open "H:\Code\Temp.csv" For Output As fHandle

    While Not RS.EOF
        sTemp = ""
        For j = 0 To RS.Fields.Count - 1
            sTemp = sTemp & RS.Fields(j).Value & sSepValue
        Next j
        If Len(sTemp) > 0 Then
            sTemp = Left(sTemp, Len(sTemp) - 1)
            Print #fHandle, sTemp
        End If
        RS.MoveNext
    Wend
   
    RS.Close
   
    Close fHandle
    MsgBox "File saved to H:\Code\Temp.csv"
   
    Me.Cls
    fHandle = FreeFile
    Open "H:\Code\Temp.csv" For Input As fHandle
    While Not EOF(fHandle)
        Line Input #fHandle, sTemp
        Me.Print sTemp
    Wend
   
    Close fHandle
   
End Sub

Private Sub Form_Load()

Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
CN.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;DRIVER=SQL Server;SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;Initial Catalog=mydb"
CN.Open
If Not CN.State = adStateOpen Then
    MsgBox "Connection Failed"
    Exit Sub
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)

CN.Close
Set CN = Nothing


End Sub

By the way, in the Select statement, I have "WHERE USER_NO = USER_NO"

The reason I did this was because I may have zero to four fields to search on, and I want to simplify the code for appending the "AND" statements.  So, I need something that I can always code as the first "WHERE" clause, something that will always be true.  So, I pick the primary key field, and say that it equals itself.  This will always be true.  Then, I can just append an "AND" for any criteria that is supplied.

This question appears to be abandoned. A question regarding it will be left in the Community Support
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <emoreau>.

The link to the Community Support area is:
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.

Thanks, emoreau  for your help here.

I have finalized this question, and will monitor it for comments.

Moondancer - EE Moderator