Solved

Exporting Data from SQL Server

Posted on 2002-03-22
5
128 Views
Last Modified: 2010-05-02
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?  

Thanks,

Zippy21
0
Comment
Question by:zippy21
  • 2
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 6889506
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
            .MoveNext
        Loop
    End With
    'Close
    Close #intFileNumber
End Sub
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6889560
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
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6889572
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.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7060595
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:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7065686
Thanks, emoreau  for your help here.

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

Moondancer - EE Moderator
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now