Creating and Writing to a text file

  • Thread starter Thread starter Tim Schiermeyer
  • Start date Start date
T

Tim Schiermeyer

I need to do the following:

Write this to a text file

"Text1 " [field data1] ",text2=" [field data 2] ",text3=" [field data 3]
",text4=" [field data 4] ... etc.. etc

The pass couple of days I have been trying to do this at a high approach for
I thought that this was my only option. I use a DDE interface approach
talking to textpad. At which point I was about an hour from completion a
co-worker sat down with me and pointed out a low level approach that lends
itself without the dependence of Textpad. He said I should use the
CreateTextFile function and the Put instructions.
My question is, is this the best approach, will this work, and potential
road blocks I can't see for my ignorance with work with VBA for about a
week? Please response. I would like full control, therefore low level and no
dependence on other programs.
Thank you
Tim Schiermeyer
Rookie
 
Tim-
I'd have to agree with your co-worker, why bother with
DDE/Textpad if you don't have to? You can use the
intrisic file functions, or use the Microsoft Scripting
library (I find this more intuitive). It's fast and easy.
I used code similar the following to generate CSV files
based on a recordset, maybe it will give you a place to
start.

<aircode>
Dim fso as FileSystemObject
Dim oFile as TextStream
dim rst as adodb.recordset
dim fld as adodb.field
dim i as integer
dim strWrite as string

Set fso = new FileSystemObject

....Create your recordset
set ofile = fso.CreateFile(yourPath & FileName
for each fld in rst.fields
strwrite = strwrite & fld.name & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite

do while not rst.eof
strwrite = vbnullstring
for each fld in rst.fields
strwrite = strwrite & fld.value & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite
rst.movenext
loop
</aircode>
HTH
Ben
 
Thanks for you input, I will give it a try. I was wondering if you know if
the is a method of calling out the the field data within ADO similiar to
DAO. That is 'rstName!fldName' for a given location within the record set?
Thus far I have not found anything within ADO that can do this. Otherwise if
using ADO I need to plant in a counter to help insert text between the field
values. Thanks again for you input.

Tim

Ben said:
Tim-
I'd have to agree with your co-worker, why bother with
DDE/Textpad if you don't have to? You can use the
intrisic file functions, or use the Microsoft Scripting
library (I find this more intuitive). It's fast and easy.
I used code similar the following to generate CSV files
based on a recordset, maybe it will give you a place to
start.

<aircode>
Dim fso as FileSystemObject
Dim oFile as TextStream
dim rst as adodb.recordset
dim fld as adodb.field
dim i as integer
dim strWrite as string

Set fso = new FileSystemObject

...Create your recordset
set ofile = fso.CreateFile(yourPath & FileName
for each fld in rst.fields
strwrite = strwrite & fld.name & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite

do while not rst.eof
strwrite = vbnullstring
for each fld in rst.fields
strwrite = strwrite & fld.value & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite
rst.movenext
loop
</aircode>
HTH
Ben
-----Original Message-----
I need to do the following:

Write this to a text file

"Text1 " [field data1] ",text2=" [field data 2] ",text3=" [field data 3]
",text4=" [field data 4] ... etc.. etc

The pass couple of days I have been trying to do this at a high approach for
I thought that this was my only option. I use a DDE interface approach
talking to textpad. At which point I was about an hour from completion a
co-worker sat down with me and pointed out a low level approach that lends
itself without the dependence of Textpad. He said I should use the
CreateTextFile function and the Put instructions.
My question is, is this the best approach, will this work, and potential
road blocks I can't see for my ignorance with work with VBA for about a
week? Please response. I would like full control, therefore low level and no
dependence on other programs.
Thank you
Tim Schiermeyer
Rookie


.
Click to expand...
Click to expand...
 
AFAIK you can still use ! notation in ADAO, but the
rstName.Fields("fieldName") or rstName.Fields(i) where i
is the field ordinal is preferable. This gives you the a
reference to the given *field* in the recordset, you need
to use the move methods to navigate amongst the rows.
I failed to mention that you must add a reference to the
MS Scripting Runtime library to use the code I posted.
HTH
Ben
-----Original Message-----
Thanks for you input, I will give it a try. I was wondering if you know if
the is a method of calling out the the field data within ADO similiar to
DAO. That is 'rstName!fldName' for a given location within the record set?
Thus far I have not found anything within ADO that can do this. Otherwise if
using ADO I need to plant in a counter to help insert text between the field
values. Thanks again for you input.

Tim

Tim-
I'd have to agree with your co-worker, why bother with
DDE/Textpad if you don't have to? You can use the
intrisic file functions, or use the Microsoft Scripting
library (I find this more intuitive). It's fast and easy.
I used code similar the following to generate CSV files
based on a recordset, maybe it will give you a place to
start.

<aircode>
Dim fso as FileSystemObject
Dim oFile as TextStream
dim rst as adodb.recordset
dim fld as adodb.field
dim i as integer
dim strWrite as string

Set fso = new FileSystemObject

...Create your recordset
set ofile = fso.CreateFile(yourPath & FileName
for each fld in rst.fields
strwrite = strwrite & fld.name & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite

do while not rst.eof
strwrite = vbnullstring
for each fld in rst.fields
strwrite = strwrite & fld.value & ","
next fld
strwrite = left(strwrite, len(strwrite) - 1)
oFile.writeline strWrite
rst.movenext
loop
</aircode>
HTH
Ben
-----Original Message-----
I need to do the following:

Write this to a text file

"Text1 " [field data1] ",text2=" [field data 2] ",text3=" [field data 3]
",text4=" [field data 4] ... etc.. etc

The pass couple of days I have been trying to do this
Click to expand...
at
a high approach for
I thought that this was my only option. I use a DDE interface approach
talking to textpad. At which point I was about an hour from completion a
co-worker sat down with me and pointed out a low level approach that lends
itself without the dependence of Textpad. He said I should use the
CreateTextFile function and the Put instructions.
My question is, is this the best approach, will this work, and potential
road blocks I can't see for my ignorance with work
Click to expand...
with
VBA for about a
week? Please response. I would like full control, therefore low level and no
dependence on other programs.
Thank you
Tim Schiermeyer
Rookie


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Tim,

Another approach is to create a query that concatenates everything you
want into one string, along these lines.

SELECT 'Text1 ' & [Field1] & ',Text2=' & [Field2] & ',text3=' .... AS
TheField FROM TheTable WHERE ... ORDER BY ... ;

Then either use DoCmd.TransferText to export the query to a textfile, or
open a recordset on the query and write each record to a textfile.

For fullest control you can build the SQL statement on the fly.

I need to do the following:

Write this to a text file

"Text1 " [field data1] ",text2=" [field data 2] ",text3=" [field data 3]
",text4=" [field data 4] ... etc.. etc

The pass couple of days I have been trying to do this at a high approach for
I thought that this was my only option. I use a DDE interface approach
talking to textpad. At which point I was about an hour from completion a
co-worker sat down with me and pointed out a low level approach that lends
itself without the dependence of Textpad. He said I should use the
CreateTextFile function and the Put instructions.
My question is, is this the best approach, will this work, and potential
road blocks I can't see for my ignorance with work with VBA for about a
week? Please response. I would like full control, therefore low level and no
dependence on other programs.
Thank you
Tim Schiermeyer
Rookie
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top