Export limited number of records to files

[ardy]

Hello All,
I am kind of lost

as to an approach to the problem I am having. I have a table with over 400,000 records. I need to export several attribute(name, date, num-1, num-2)from the table to *.csv or *.xls files with no more than 1000 record for each exported file. Can some body give me direction and or code to start me with. and I will modify. I do have some understanding of Access and VBA but i am not an expert.......Any help is appreciated.

 

[ardy]

I found this code in a tread back in 2005 which works pretty good, Thanks to DCrake, Would it be possible to have the output be *Xls and have the hedder to print in each file. The files seem to be Data only.

Public Function SplitTableOrQuery(TableOrQueryName As String, MaxRows As Long)
Dim Rs As DAO.Recordset

Dim fileNum As Long
Dim TxtHedder As String
Dim TxtStr As String
Dim nIndex As Long
Dim StrPath As String
Dim nFile As Long

StrPath = CurrentProject.Path

Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)

If Not Rs.EOF And Not Rs.BOF Then
    'Calculate the number of text files required to complete task
    Rs.MoveLast
    
    nFile = Int(Rs.RecordCount / MaxRows) + 1
    fileNum = 1
    Rs.MoveFirst
    
    'Open the first instance of the text file
    Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    
   Do Until Rs.EOF
    
    For n = 0 To Rs.Fields.Count - 1
    
        TxtStr = TxtStr & CStr(Nz(Rs(n), "")) & ","
        
    Next
    'Drop the last comma
    TxtStr = Left(TxtStr, Len(TxtStr) - 2)
    
    Print #fileNum, TxtStr
    TxtStr = ""
    
    nIndex = nIndex + 1
    Rs.MoveNext
    If nIndex > MaxRows Then
        nIndex = 0
        Close #fileNum
        fileNum = fileNum + 1
        Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    End If
   Loop
   Rs.Close
End If
Set Rs = Nothing
Close #fileNum
 
End Function

 

[Frothingslosh]

I'm about to head out to watch Star Wars again, but really quick:

To export to Excel rather than a text file, look HERE for how to open/create an Excel workbook and work with it.

As to getting the data out, look into CopyFromRecordset. You'll need to export a thousand rows, advance 1000 rows in the recordset, and keep repeating until done.