Automate Output of text files from an Access query

[Fiat77]

We have an Access Query named z_Union_TextFiles_Trailer with 4 fields. We need to output just one field (field name is DataSet) to a text file and save the text file using the value of the field Grouping. Dataset record count vaires for each group.

Example Grouping field value GroupA has 200 records output all 200 records for field DataSet to text file Save the file name C:\GroupA.txt, then go the next Grouping field value which is GroupB output its 219 records save the file name C:\GroupB.txt and continue the loop until the end of Groupoing Field Values.

How can i do this by calling a procedure?
Thanks

 

[Fiat77]

I have 4 fields in my query z_Union_TextFiles_Trailer they are Grouping, Sort, ID and Dataset

I just need to output the contents of the Dataset fieldwith a break and save as to a new text file name at every change in the Grouping Field Value.

Thanks

 

[mikej336]

It sounds like you understand that you need to open the record set, loop thru the record set, opening and closing a text file each time your grouping changes.

Goto

http://www.vba-programmer.com

and you will find examples of opening a recordset, opening and closing text files. Throw in a DO While loop and your done....

If I'm missing the point please post more....

Uncle Mike

 

[JerryKlmns]

Hi Fiat77,
a quick thought

Dim rstRead As ADODB.Recordset
Dim rstWrite As ADODB.Recordset
Dim fso As Object
Dim file As Object

'Get field's [Grouping] all entries

Set rstRead = New ADODB.Recordset
With rstRead 
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = "Select Distinct [Grouping] From z_Union_TextFiles_Trailer;"
    .Open
End With

Read z_Union_TextFiles_Trailer query data

Set rstWrite = New ADODB.Recordset
With rstWrite
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = "Select Grouping, Dataset From z_Union_TextFiles_Trailer;"
    .Open
End With

Set fso = CreateObject("Scripting.FileSystemObject")

For every value of rstRead

While Not rstRead.EOF

Filter rstWrite

    rstWrite.Filter = "[Grouping]= '" &  rstRead.Fields(0) &"'"

create a TextStream object

    Set file = fso.CreateTextFile(File_Location & rstWrite.Fields(1))

to write all of rstWrite for that rstRead value

    While Not rstWrite.EOF
        file.WriteLine (rstWrite.Fields(1))
        .MoveNext
    Wend

close file & remove filter and move to the next record of rstRead

    file.Close
    rstWrite.Filter =""
Wend

Clear objects

Set rstRead = Nothing
Set rstWrite = Nothing
Set file = Nothing
Set fso = Nothing

Code not tested.

 

[JerryKlmns]

A [red]correction[/red] from

Set file = fso.CreateTextFile(File_Location & rstWrite.Fields([red]1[/red]))

to

Set file = fso.CreateTextFile(File_Location & rstWrite.Fields([blue]0[/blue]))

 

[Fiat77]

Thanks for the help but I am wondering where is the File Path? Where do these files get created? Thanks

 

[JerryKlmns]

Fiat77

This variable File_Location holds your desired path.
Just

Dim File_Location as string
File_Location="C:\ExportedRecordsets\"
Set file = fso.CreateTextFile(File_Location & rstWrite.Fields(0) & ".txt")