Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    297
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Search Database and write recordset to csv file.

    Hi All,

    I need to search my access database and write the resulting recordset to a csv file, ready for attaching to an email. Here is the code I am using:

    ======================

    Dim oFSO
    Dim oFile
    Dim oField
    Dim oconn
    Dim ors
    Dim strOutput
    Dim strdbpath
    Dim strSQL

    strdbpath = "d:\data\data.mdb"

    Set oconn = CreateObject("ADODB.Connection")
    oconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strdbpath & ";"

    strSQL = "SELECT ref, name FROM addressfile WHERE status = 'Current';"
    Set ors= oconn.Execute(strSQL)

    ' ********** Start of code I found ********

    set oFSO = createobject ("scripting.filesystemobject")
    set oFile = oFSO.createtextfile("d:\data\record.csv",true)

    do until ors.eof
    for each oField in ors.fields
    stroutput = stroutput & ofield.value & ","
    next
    'remove the last comma from the line before continuing
    stroutput = left(stroutput,len(stroutput)-1)
    ofile.writeline stroutput
    ors.movenext
    loop

    ofile.close

    set oFile = nothing
    set oFSO = nothing

    ' ********** End of code I found ********

    ors.Close
    Set ors = Nothing

    oconn.Close
    Set oconn = Nothing

    ================================

    I found the script between the ********** in a tutorial on writing CSV's, then topped and tailed it with the connection, etc code.

    I named this script "makecsv.vbs". When I run this file on the server, it correctly creates the file "d:\data\record.csv" but the file does not contain any records.

    The sql query works OK by itself, I tried it in my database manager programme and it returned the correct records.

    What have I missed out and where is the "oField " defined?

    Regards

    Gary

  • #2
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    297
    Thanks
    10
    Thanked 0 Times in 0 Posts
    HI Guys,

    Solved it! Hunting around the net and a bit of experimenting got the following file working a treat. Produces an nice little table as well, it does.

    Regards

    Gary

    ========================

    Dim oFSO
    Dim oFile
    Dim oField
    Dim oconn
    Dim ors
    Dim strOutput
    Dim strdbpath
    Dim strSQL
    Dim strLine

    strdbpath = "d:\data\data.mdb"

    Set oconn = CreateObject("ADODB.Connection")
    oconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strdbpath & ";"

    ' Select the records to be written to the CSV file

    strSQL = "SELECT field1, field2 FROM tablename WHERE field3= 'somevalue' ;"
    Set ors= oconn.Execute(strSQL)

    set oFSO = createobject ("scripting.filesystemobject")
    set oFile = oFSO.createtextfile("d:\data\record.csv",true)

    strLine="<table><tr>"

    For each oField in ors.fields
    strLine= strLine & "<td>" & oField.name & "</td>"
    Next
    strline=strline&"</tr>"
    ofile.writeline strLine

    Do while Not ors.EOF
    strLine="<tr>"
    for each oField in ors.Fields
    cell=oField.value
    strLine= strLine & "<td>" & cell & "</td>"
    next
    strline=strline & "</tr>"
    ofile.writeline strLine
    ors.MoveNext
    Loop
    ofile.writeline "</table>"


    ofile.close

    set oFile = nothing
    set oFSO = nothing

    ors.Close
    Set ors = Nothing

    oconn.Close
    Set oconn = Nothing

    =========================


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •