TransferText in Access 2000.....please bear with me 1

[PerryG]

I know this topic has been covered before in one of the forums, but for the life of me, I can't find it and I can't get the recommended solution to work.

History: Using TransferText in Access 2000 to import or export you can receive error messages, and if you want to use a schemi.ini file for specific formatting, it will be ignored. This is a known problem in the MS Knowledge Base #241477.

The recommended resolution is to use the db.execute......example:
db.execute "Select * INTO tblNEW FROM [Text; FMT=Delimited;HDR=Yes;Database=c:\mydocuments;];"

I assume it works fine for importing but I haven't been able to make it work for export.

Is it even possible to use this for export? I need to be able to export in a specific format, i.e., pipe delimited, no text qualifier, first line includes column names. I've been working on this off and on for about 6 weeks, and at this point I'm too close to it to see where my blind spot is.

Any and all help is greatly appreciated.

Thanks.

 

[coco86]

I haven't ever had this problem. I don't use a "schemi.ini" file but I do use an export specification I set up in my database and it's always worked fine for me.


To answer your question, though, you could do it this way (use whatever names you want, I'm just making suggestions)

Create a filesystem object named FSO and a textstream named Output
Create a recordset object named RS

To export the fieldnames:

For i = 0 To RS.Fields.Count - 2
Output.Write RS.Fields(i).Name & "|"
Next i
Output.Write RS.Fields(i).Name & vbcrlf

To export the data:

' Loop through records and ouput 1 row per record
Do While Not RS.EOF
For i = 0 To RS.Fields.Count - 2
Output.Write RS.Fields(i).Value "|"
Next 'I
Output.Write RS.Fields(i).Value
RS.MoveNext
If Not RS.EOF Then
Output.Write vbCrLf
End If
Loop

 

[PerryG]

coco86 -
Thanks for the input, but for the moment, let's pretend I shouldn't know what you are referring to when you say 'textstream'. I am a little vague on the term and I want to be sure we are talking about the same thing. Can you give me an example how you declare it? I normally make use of the embedded tools and have little experience writing out to text.

Thanks for you input and patience.

 

[coco86]

A textstream is one of the objects in the Microsoft Scripting Runtime Library. To create one, make sure you've set your reference (tools => references)

Add the following lines to your code

Dim fso As New FileSystemObject
Dim output As TextStream

Set output = fspenTextFile("filename.txt", ForWriting, True)

The OpenTextFile method of the File System Object creates a textstream that you can read to or write from. The first argument is your file (full path). The second argument says what you want to do with the stream, read or write. The third argument, when true, says to create the file if it doesn't already exist.

Hope that helps some.

 

[PerryG]

Thanks! Much clearer now.

PerryG