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

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

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