Export Large Table to Multiple Small Text Files

[dgr]

Hi,
I have a table of thousands of records in MS Access 2007. I want to export it to a text file consisting of 500 records per text file. I'm looking for a macro which can do this. The macro should ideally create the text files automatically.

I've gone as far as using the following code which works well to export the entire table into one text file. I don't know how to break up this big exported text file into smaller files of 500 records per file automatically during the import.

Thank you for your advice.

Sub ExportToText()
Dim rst As DAO.Recordset
Open "C:\Users\a\Desktop\test.txt" For Output As #1
Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM [Australian Non Duplicate Names]", dbOpenSnapshot)
Do While Not rst.EOF
   Print #1, rst!Name
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Close #1
End Sub

 

[vaskov17]

not tested, but try this;

Sub ExportToText()
    Dim rst As DAO.Recordset
    Dim row_num As Integer
    Dim file_num As Integer
    
    Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM [Australian Non Duplicate Names]", dbOpenSnapshot)
    
    file_num = 1
    row_num = 1
    
    Open "C:\Users\a\Desktop\test" & Format(file_num, "00") & ".txt" For Output As #1
    
    Do While Not rst.EOF
        If row_num Mod 500 = 0 Then
            Close #1
            file_num = file_num + 1
            Open "C:\Users\a\Desktop\test" & Format(file_num, "00") & ".txt" For Output As #1
        End If
        
        Print #1, rst!Name
        rst.MoveNext
        row_num = row_num + 1
    Loop
    
    rst.Close
    Set rst = Nothing
    Close #1
End Sub

 

[dgr]

Thank you very much, Vaskov17. This was exactly what I was looking for. I appreciate your help.

 

[dgr]

Hello,
I now have a table with 4 fields. The field names are Name, Phone, NRIC and Address1. How do I edit the same code above to export all fields as a tab delimited text file? I tried changing this line:

Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM [Australian Non Duplicate Names]", dbOpenSnapshot)

to

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Australian Non Duplicate Names]", dbOpenSnapshot)

but it didn't work. Only the 1st field was exported.

Thanks for your help.