Solved

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

Posted on 2014-03-19
7
1,688 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

803 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