How to split recordsets and export

[cowspotter]

I am trying to parse though a table which contains user activity for a week. I am running a query against the table which returns the unique user ids that i will generate reports for. These results fill idRS (recordset). I then loop through each one filling activityRS with just that IDs activity. I have a sub that I found which exports the provided recordset as an excel document. The problem I have is that there are times where a given ID will have more than 50,000 rows. Its entirely possible for an ID to have >150,000 records. Because of Excels row limit I need to be able to split the recordset across multiple excel files. These files are provided to users so i can't just have them open the files in access. How do I split a 150,000 recordset into 3 identically formed recordsets (ie. Fields) with each set having 50,000 rows each. Here's the code I have so far. I haven't included the export to excel sub but if you need that, just let me know:

Public Function CreateReports()
Dim db As Database
Dim idRS As Recordset
Dim rsField As Field
Dim activityRS As Recordset
Dim activityRS1 As Recordset
Dim activityRS2 As Recordset
Dim activityRS3 As Recordset
Dim activityRS4 As Recordset
Dim count As Integer
Set db = CurrentDb()
Set idRS = db.OpenRecordset("SELECT [All Data 2].[DB User Name] As user_name FROM [All Data 2] GROUP BY [All Data 2].[DB User Name];", dbOpenDynaset, dbDenyRead)
If Not (idRS.BOF And idRS.EOF) Then
idRS.MoveFirst
Do While Not idRS.EOF
Set activityRS = db.OpenRecordset("SELECT [All Data 2].* FROM [All Data 2] WHERE [All Data 2].[DB User Name] = '" & idRS("user_name") & "';", dbOpenDynaset, dbDenyRead)
activityRS.MoveLast
count = activityRS.RecordCount
activityRS.MoveFirst
If count < 50000 Then
SaveAsExcel activityRS
ElseIf count >= 50000 And count < 100000 Then
ElseIf count >= 100000 And count < 150000 Then
ElseIf count >= 150000 And count < 200000 Then
End If
idRS.MoveNext
Loop
End If

End Function

____________________________

The numbered activityRS recordsets are what I created to hold the split recordset data, just don't know how to go about splitting the data.

 

[tranchemontaigne]

You may not like this solution - I don't have a code snippet for you either but.....here is a piece of pseudocode

(1) Create a recordset of all records "rst1"
(2) loop through "rst1" and count each record with a loop counter
(3) based upon an evaluation of the loop counter value, write records into other recordsets (e.g. rst_Less_Than_50k", rs_50k_to_100k, rs_100k_to_150K)

intLoopCount = 0
do until rst1 =EOF

if intLoopCount <50000 then
rst_Less_Than_50k.addnew (syntax I don't know)

elseif intLoopCount >49999 and intLoopCount <100000 then
rs_50k_to_100k.addnew (syntax I don't know)

else
rs_100k_to_150K.addnew (syntax I don't know)

endif

intLoopCount = intLoopCount +1
rst1.movenext

loop

You should be able to fill in gaps in my knowledge from the following article http://msdn.microsoft.com/en-us/library/z5139kz6(VS.80).aspx
________
Medical marijuana

 

[The_Doc_Man]

There are other ways to skin this cat. (MMMEEEEEOOOOOWWWWRRRRR!! sorry, kitty)

I'm going to give you an overview and a few keywords to look up. In this, I will presume that you know ahead of time the structure you want to write in the spreadsheet. I.e. you know column headers and row headers and the general format of what you were going to write.

1. In your VBA, create an Excel Application - look up "Application Object" - and understand that the Application Object is going to activate Excel as a "child" process of Access while that object is open. Use the help files to explore the Excel Application object, and also get into Excel itself to explore its VBA topics such as the objects exposed to ActiveX (automation) control.

Brief diversion: A recordset is a pointer to a collection of records. A recordset has a collection of fields. You are already working with collections, you just don't know it. Well, an Excel object is a task that can have a collection of open workbooks, one of which will be active. A workbook is an object that can have a collection of worksheets, one of which will be active and can become the "ActiveSheet" - a keyword similar to the "Me" keyword in forms. An Excel worksheet is a collection of rows which contain collections of cells. It is ALSO (simultaneously) a collection of columns which contain collections of cells. A cell is a collection of properties including a way to dump text into that cell from outside. So read up "collections" in the help files if you have not run into this concept before.

2. In the application, use the method that creates a new workbook. I believe it is just the .Open (method) where you give it a name that doesn't exist and give it permission to create the file if it doesn't exist. Read up on the parameters in the Excel VBA objects.

3. Once the workbook is open, create a spreadsheet. Make it the "ActiveSheet"

4. OK, the structure of a workbook is a sequence of rows and columns, right? BUT actually not exactly right. If you start by working with the rows, the intersection with a given column is a cell on that row. If you start by working with the columns, the intersection with a given row is a cell in that column. I.e. ActiveSheet.Row(2).Cell(3) is the same as ActiveSheet.Column(3).Cell(2). Using this concept, you can random-access the contents of the active sheet knowing column number and row number.

5. A cell is actually a range that might include text. Look this up in Excel's VBA help, but basically you write what you want in the cells by random-accessing them as noted above, with the occasional .AddNew to the Rows or Columns collection depending on where you are going and how. Just dump your desired contents into each cell. If you want to be extremely careful, always dump CStr$(x) or Format$( template, x ) into the cell, unless you are certain that the thing you are dumping is already a string.

6. Don't forget to add titles to your top row and/or left column. You can dump a constant into a cell just as easily as anything else. Again, watch for string vs. numeric modes on all data elements.

7. Inside the VBA, just COUNT how many rows you have written. (You have to anyway, since that is your row index...) When you reach your limit, create a new sheet, make IT the activesheet, and continue your loop with the row and/or column indexes reset.

8. When done, don't (EVER) forget to close what you open. Which is to say both the Excel application AND the recordset that drove it. The application object that you used to work with Excel should also be set to "Nothing" - the VBA equivalent of what is called "deferencing a pointer."

All of this sounds like a complex piece of code. But you were already in VBA anyway.