Solved

Writing an entire recordset to a .txt file - vbs file help

Posted on 2014-03-19
7
1,496 Views
Last Modified: 2014-04-16
Hi

I am attempting to write a vbs file to ADO connect to a database, return the results of a query and write out the recordset to a text file.

I subsequently want to print the text file to generic (text only) printers with no formatting.

I have written some (probably messy) code to connect and populate a recordset. I have tested this and I can echo out fields from the first record.

How do I now get this data out of an array in memory into a text file?

do until eof / rs.movefirst / movenext do not work (I believe due to the oConn parameters).

I believe that oRec.Fields(0) oRec.Fields(1) may help but I cant work out the exact syntax.

Any ideal people??

Option Explicit

Dim sConn
Dim oConn, oRec

Dim sQuote, sSQL

sConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\SERVER\database.mdb;"

Set oConn = CreateObject("ADODB.Connection")
Set oRec = CreateObject("ADODB.Recordset")

sQuote = Chr(39)
sSQL = "SELECT * FROM FIRELIST"

oConn.Open sConn
oRec.Open sSQL, oConn, 0, 1, 1

Wscript.echo oRec("EmploeeName")

oRec.Close
oConn.Close

Open in new window


Thank you for your help
0
Comment
Question by:noooodlez
  • 3
  • 2
7 Comments
 
LVL 18

Accepted Solution

by:
Peter Hutchison earned 250 total points
ID: 39939319
Use the following lines e.g.

Set fs = CreateObject("Scripting.FileSystemObject")

Set obj=fs.OpenTextFile("c:\employees.txt")
obj.Writeline (oRec("EmploeeName")
obj.Close
0
 

Author Comment

by:noooodlez
ID: 39939353
Good start!!!!

Set obj=fs.OpenTextFile("c:\employees.txt", 8)

Got it working with a minor tweak.

Now, I have the employee name of the first record in the recordset landing in my text file.

How do I get it to dump out the ENTIRE ARRAY (rs) into my text file??

Many thanks
Steven
0
 
LVL 18

Expert Comment

by:Peter Hutchison
ID: 39939410
~Something like this

while not orec.EOF
  for field = 0 to orec.Fields.Count
    obj.writeline(orec.Fields.Item(field))
  next field
  orec.NextRecord
wend
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:noooodlez
ID: 39939512
right, got this far....

Option Explicit

Dim sConn
Dim oConn, oRec, fs, obj

Dim sQuote, sSQL

sConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\SERVER\Databases\Firelist.mdb;"

Set oConn = CreateObject("ADODB.Connection")
Set oRec = CreateObject("ADODB.Recordset")

sQuote = Chr(39)
sSQL = "SELECT * FROM FIRELIST"

oConn.Open sConn
oRec.Open sSQL, oConn, 0, 1, 1


Set fs = CreateObject("Scripting.FileSystemObject")

Set obj=fs.OpenTextFile("C:\temp\Firelist\firelist.txt", 2)


while not orec.EOF

  'for field = 0 to orec.Fields.Count
    obj.write(orec.Fields.Item("Muster"))
    obj.write("          ")
    obj.write(orec.Fields.Item("ClockedOn"))
    obj.write("          ")
    obj.write(orec.Fields.Item("Present"))
    obj.write("          ")
    obj.write(orec.Fields.Item("Name"))
    obj.write("          ")
    obj.write(orec.Fields.Item("Arrived"))
    obj.write("          ")
    obj.write(orec.Fields.Item("Left"))
  'next' field
  orec.NextRecord

wend 

obj.Close 

oRec.Close
oConn.Close

Open in new window


If I comment out the WHILE statement it writes out the first (Current) line. oRec.nextrecord method doesn't work (nor does movefirst, movelast).

I believe the type of connection

oRec.Open sSQL, oConn, 0, 1, 1
establishes whether I can navigate through the recordset.

In the back of my mind I though there was a way of calling the record number from the array oRec(0), oRec(1) or similar.
0
 

Author Comment

by:noooodlez
ID: 39939541
Forget that.
I was thinking of Wscript.Arguments(0) Wscript.Arguments(1) where you can specify which argument to use.

How do I get the script to loop / write out each record in the recordset??
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40003489
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

10 Experts available now in Live!

Get 1:1 Help Now