Create files 'on the fly' 1

[StuartBombay]

I have a recordset with information on about 20,000 people, and each of those people belong to one of about 30 different post offices. I would like to create a tab del file for each post office with the file named after the post office and populated with the people who belong to it.
I would like the file name to be created on the fly, by reading the post office field name. I can have the record set sorted by post office easily enough so I should be able to just create the file, fill it up, close it and move on to the next.
I'm looking for suggestions. I have a couple ideas on how to do this, but would like to draw on some of your experience as well. Mostly, my brain is tired after getting the recordset together in a useful manner and have now hit the wall. If anyone can dash off a couple of lines to get me started I would be eternally grateful!
I picture it being something like:

open the recordset
read the the first file
put the PO in a variable
create the file 
put the row in the file
read next
compare the PO to the variable
if it's the same put the row in the file
if it's different
   close the file
   reload the variable
   create a new file
etc

Am I on the right track?

 

[Remou]

Why not:

Select from file where po ...
DoCmd.TransferText ...

 

[StuartBombay]

Yes, that makes sense, the docmd does a lot of work for you, and it's perfect for that part of the job. I'm foggy on how to collect the post office names. I want to collect those from the recordset rather than another table because those can change.
Should I run through the recordset once first and collect the post office names into an array, then step through that array using each of it's records as a variable in the 'Select from..where po = '(variable)?
Is that the most efficient way, or is there a better way?

Thanks for the help.

 

[Remou]

Please provide some details about the recordset, where is coming from? What is the SQL? Also, have you a separate list of POs?

For the most part, it is best to avoid stepping through anything, it is very slow.

 

[StuartBombay]

The file is a list of students, and the PO matches their school. As a school is added to the program it's students will be in that list. That is figured out up the line, and would be best not to have to keep track of it in this database.
So the file I have is
stID, First, Last, SchoolID, PO

I'm exporting all but the PO, the file is named after the PO.
Here is what I have so far:

       Dim stOU As String
    Dim stNew As String
    Dim stSpec As String
    Dim stDir As String
    Dim stSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim OUNames As Object
    Dim x As Integer

'make sure the recordset is in OU order
''you may as well specify the order of fields too
    stSQL = "select * from qryNovellStudents order by OU"
    Set db = CurrentDb

    Set rs = db.OpenRecordset(stSQL, dbOpenDynaset)
    Set OUNames = CreateObject("scripting.dictionary")

    stSpec = "ou export specification"
    stOU = " "
    stNew = " "
    x = 0
'create a COLLECTION to store all of the ou's
    Do While Not rs.EOF
        'set the ou
        stOU = rs.Fields("OU")
        If stOU <> stNew Then
            OUNames.Add x, stOU
            stNew = stOU
            ''move the docmd to here
        End If
        x = x + 1
        rs.MoveNext
    Loop
'Go back to the biginning of the recordset
    rs.MoveFirst
'For each ou name create a file and fill it up
    For Each ou In OUNames.Keys
        stSQL = "SELECT PUPIL_NUMBER, FIRST_NAME, "
        stSQL = stSQL & "SECOND_NAME, SURNAME, "
        stSQL = stSQL & "LONG_NAME, SCHOOL "
        stSQL = stSQL & "FROM rs where ou ='" & OUNames(ou) & "'"

'Create the directory here where you have the OU name
        stDir = "X:\TechOps\LTSP\StudentAccounts\" & OUNames(ou) & ".tab"
        Debug.Print stSQL
        Debug.Print stDir
        DoCmd.TransferText acExportDelim, stSpec, stSQL, stDir
    Next
    rs.Close
    Set rs = Nothing
    Set OUNames = Nothing

Right now the docmd.transfertext isn't working, the error says MS Access could not find the object..(spells out the SQL). The SQL is good if I copy it and run it in the grid. Once I get that working I think I can move the docmd into the WHILE and therefore skip storing the PO names.
Should that work well? Any idea why I'm getting that error?
BTW, thanks for your help. I do appreciate it.

 

[Remou]

You need to use the sql with a query:

QueryName="tmpExport"
    If IsNull(DLookup("Name", "MsysObjects", "Name='" & QueryName & "'")) Then
        CurrentDb.CreateQueryDef QueryName, strSQL
    Else
        CurrentDb.QueryDefs(QueryName).SQL = strSQL
    End If

You can then export tmpExport.

To get PO, I would suggest using something on the lines of:

'Go back to the biginning of the recordset
    rs.MoveFirst
'For each ou name create a file and fill it up
    For Each ou In OUNames.Keys
        strSQLPO= "SELECT DISTINCT PO " _
        & "FROM rs where ou ='" & OUNames(ou) & "'"

        Set rsPO=CurrentDB.OpenRecordset(strSQLPO)
        Do while Not rsPO.EOF
           stSQL = "SELECT PUPIL_NUMBER, FIRST_NAME, "
           stSQL = stSQL & "SECOND_NAME, SURNAME, "
           stSQL = stSQL & "LONG_NAME, SCHOOL "
           stSQL = stSQL & "FROM rs where ou ='" & OUNames(ou) & "' AND PO=" & rsPO!PO

          'Export here
          rsPO.MoveNext
       Loop

I have just sketched the above, so hopefully you get the general idea.

 

[StuartBombay]

Thanks Remou, I think I'm getting the idea. I'm having a problem with the query def. I want it to query the original recordset that is defined at the start:

    stSQL = "select * from qryNovellStudents order by OU"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(stSQL, dbOpenDynaset)

But I don't think refering to -rs- in the sql for the query def is the right thing:

        QueryName = "tmpExport"
            If IsNull(DLookup("Name", "MsysObjects", "Name='" & QueryName & "'")) Then
                db.CreateQueryDef QueryName, stSQL
            Else
                db.QueryDefs(QueryName).SQL = stSQL
            End If

        DoCmd.TransferText acExportDelim, stSpec, "tmpExport", stDir

I now have an error that says it can't find the input table or query 'rs'. That makes sense because in this context 'rs' is not interpreted as the recordset itself but rather the name of one (I think). However, I'm not sure how to correctly refer to that set in stSQL.

Sketches of the general idea are great. A point in the right direction is the best learning help.

 

[StuartBombay]

Doh! Never mind.
I referred to the query name instead of rs and it built the query as expected.
Sorry - but I get it now!
Always good to track back as see what the code it doing!