Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-19
7
Medium Priority
?
2,384 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 20

Accepted Solution

by:
Peter Hutchison earned 1000 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 20

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…
Suggested Courses

927 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