Link to home
Start Free TrialLog in
Avatar of MrAutomate
MrAutomateFlag for United States of America

asked on

How do I split query result into multiple text files using VBA

Experts:

I have a query that returns 465K records.  I need to export the results as tab delimited, but the records need to be imported to a mainframe, with a miximum transaction limit of 90,000 per batch.

Is it possible to have query :qry_test output the following fields as tab delimited, and create as many text files as required? so, 465,000/90,000 = 6 text files, output_1.txt, output_2.txt, etc, etc/

qry_test consists of 2 fields:
Ln_Num
Default_Value

Thanks
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Is Ln_num a sequential number?
If so the simplest approach is to use multiple queries each with a defined range , rather than code.
Avatar of MrAutomate

ASKER

Peter57r:
The ln_Num is not sequential, but I could sort by ln_num, but this will be a monthly process going foward, so I would not be able to predict the number of records, so I think code will be required, unless you have other  thouhts on how to generate a query on the fliy, based on the number of records returned?  Tnx
ASKER CERTIFIED SOLUTION
Avatar of shaydie
shaydie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this may run faster,
you have to create an export specification first ( the code uses the "query1 export specification")

also create a query with this statement

select * from yrtable

and save as "query1"


Sub expQ()
Const maxRec = 30000
Dim qd As DAO.QueryDef, db As DAO.Database, ssql As String, vTextFile
Dim j As Long, n As Integer, vRecCount As Long, maxLnNum As Long
Set db = CurrentDb

vRecCount = DCount("*", "yrTable")
j = IIf(Int(vRecCount / maxRec) = vRecCount / maxRec, vRecCount / maxRec, vRecCount / maxRec + 1)
For n = 1 To j
vTextFile = "C:\" & "output_" & n & ".txt"
Set qd = db.QueryDefs("query1")
    If n = 1 Then
        ssql = "select top " & maxRec & " * from yrtable"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True
    Else
        ssql = "select top " & maxRec & " * from yrtable where Ln_num >" & maxLnNum
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True

    End If
Next n


End Sub

Open in new window

better have an order by

select * from yrtable order by Ln_num

and save as "query1"

Sub expQ()
Const maxRec = 90000
Dim qd As DAO.QueryDef, db As DAO.Database, ssql As String, vTextFile
Dim j As Long, n As Integer, vRecCount As Long, maxLnNum As Long
Set db = CurrentDb

vRecCount = DCount("*", "yrTable")
j = IIf(Int(vRecCount / maxRec) = vRecCount / maxRec, vRecCount / maxRec, vRecCount / maxRec + 1)
For n = 1 To j
vTextFile = "C:\" & "output_" & n & ".txt"
Set qd = db.QueryDefs("query1")
    If n = 1 Then
        ssql = "select top " & maxRec & " * from yrtable order by Ln_num"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True
    Else
        ssql = "select top " & maxRec & " * from yrtable where Ln_num >" & maxLnNum & " order by Ln_Num"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True

    End If
Next n


End Sub

Open in new window

Shaydie:

It appears to have properly created output_1.txt, but stops there.  What needs to be done to have it loop through all the records, and created all the text files?  Many thanks!
Shaydie:  My bad.  I had hard coded a value in the query and forgot to clear out the criteria, your solution works like a charm!!
MrAutomate,

did you try the codes posted at http:#a33930717
Solution was complete, timely, and worked perfectly.  Thanks again!
Avatar of shaydie
shaydie

Glad that worked for you.. Thank you!
capricorn1:  

Thanks for the information.  I happened to view the suggestions in order, and the first suggestion was the simpler solution, avoiding creating an export, etc.  Thanks again for yout ideas!