Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

Exporting Data from SQL Server

I have a table in Microsoft SQL server with the following fields...first ; last ; middle ; ss# ; address ; phone ..etc..

I have a form with text boxes where you either enter the ss# or the first;last;middle name.  I need to know how I would tell the SQL server to take either the records for the ss# or the name that I entered and export it into a text delimited format?   I want all the fields for that person....could someone give me an example?  


  • 2
  • 2
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
if you have an ADO recordset containing the data you want to be copied to a CSV file, just pass it to this function:

Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal pstrFileName As String)
Dim intFileNumber As Integer
Dim lngLoop As Long
Dim strX As String

    If Len(Trim$(pstrFileName)) = 0 Then pstrFileName = App.Path & "\zzRst.csv"
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFileName For Output As #intFileNumber
    'Write data to file
    With prstData
        strX = ""
        For lngLoop = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop).Name & ", "
        Next lngLoop
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & ", "
            Next lngLoop
            Print #intFileNumber, strX
    End With
    Close #intFileNumber
End Sub
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
    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
    Close fHandle
    MsgBox "File saved to H:\Code\Temp.csv"
    fHandle = FreeFile
    Open "H:\Code\Temp.csv" For Input As fHandle
    While Not EOF(fHandle)
        Line Input #fHandle, sTemp
        Me.Print sTemp
    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"
If Not CN.State = adStateOpen Then
    MsgBox "Connection Failed"
    Exit Sub
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)

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.
Éric MoreauSenior .Net ConsultantCommented:
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:

Thanks, emoreau  for your help here.

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

Moondancer - EE Moderator

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now