Solved

Exporting Data from SQL Server

Posted on 2002-03-22
5
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 70

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 70

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 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