Solved

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

Posted on 2014-03-19
7
1,596 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 19

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 19

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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…
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…

863 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

27 Experts available now in Live!

Get 1:1 Help Now