Split Recordset Every 50 Records

Question

I know how to use access VBA to get the total count of records in the recordset. Which is what I do below, but I am in need of taking my main rs and splitting the rs into sub-recordsets every 50 records. Meaning if rs.RecordCount = 200 - then I would want to create, rs1, rs2, rs3, rs4 and the recordCount of each should be 50.

How can I dynamically split a recordset into sub-recordsets as desecribed above? This is how I get the count in the primary recordset

    Set rs = CurrentDb.OpenRecordset("Select firen, daja, globaltwo", dbOpenDynaset)

With rs
   If Not .EOF And Not .BOF Then
      .MoveLast
      .MoveFirst
      Do While Not .EOF
        qty = rst.RecordCount
        Debug.Print qty
      Loop
   End If
End With

rs.Close
Set rs = Nothing

Possible duplicate of [MS Access LIMIT X, Y](https://stackoverflow.com/questions/8627032/ms-access-limit-x-y) (you should limit _before_ you open the recordset, not after) — Erik A, Nov 14 '17 at 20:49
@ErikvonAsmuth -> My table has no primary key field or autonumber field, so I do not think that I can utilize the poss dupe example you linked to. — IcyPopTarts, Nov 14 '17 at 20:50
That's relevant information that you should provide in the question. Anyway, if there's no way to identify records, I highly doubt this is even possible without weird trickery that will slow down your database (such as opening an empty recordset and moving over records one by one, which you do not want to do). — Erik A, Nov 14 '17 at 20:54
@Mat'sMug - was trying to use VBA to add a auto-number PK field with this syntax "DoCmd.RunSQL ("Alter Table [_SpreadsheetImport] Add Constraint TempID Primary Key(TempID)")" but I get an error of Invalid field definition "TempID" in definition of index or relationship — IcyPopTarts, Nov 14 '17 at 21:00
FWIW unless you need to read the `.Count` property before you start iterating the recordset, that `.MoveLast`/`.MoveFirst` combo is confusing and useless. Remove it. As for the PK syntax, that looks like something SQL Server would understand. I doubt Access uses the same `alter table / add constraint` syntax. — Mathieu Guindon, Nov 14 '17 at 21:02
So adding in an auto-number PK field will allow me to distinguish between record counts, but how would I determine how to split every 50 records? — IcyPopTarts, Nov 14 '17 at 21:05
What do you want to do with the 50-each recordsets? I have trouble imagining a task that couldn't be done with the full recordset, or with a completely different solution. — Andre, Nov 14 '17 at 21:21
@Andre beat me to it. If you need to do this then it's probably an indication that you have to change the design of the code. — Pat Jones, Nov 14 '17 at 21:22
The proper syntax for creating an auto-incrementing primary key is the following: `ALTER TABLE [_SpreadsheetImport] ADD COLUMN [TempID] AUTOINCREMENT(1,1) PRIMARY KEY` — Erik A, Nov 14 '17 at 21:57
@Mat'sMug The `Alter Table/Add constraint` (documented [here](https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx)) is pretty similar, and looks valid, but the error probably is that you can't add a constraint without creating the field first (and might as well add it while creating) — Erik A, Nov 14 '17 at 22:16
@Andre - I am needing to export to Excel - but limit to 50 columns in Excel. So my thought was to split the recordset every 50 records and create a sub-recordset and have that data export to a seperate Excel workbook — IcyPopTarts, Nov 14 '17 at 23:39
@Mat'sMug - I am sorry if the answer is already provided in linked answer. I do not seem to be able to apply the answer in the linked post to my situation — IcyPopTarts, Nov 15 '17 at 00:04
Records would be *rows*, not columns... now I don't even know what the question is about... You might want to clarify that. Good luck! — Mathieu Guindon, Nov 15 '17 at 00:23
@Mat'sMug - I am taking the 3 columns returned from the recordset and filling across Row 4 in Excel. When in Excel the column num reaches 50 - I need to stop writing data to this workbook and begin writing to a new one. Does that help clarify? — IcyPopTarts, Nov 15 '17 at 00:33
_Does that help clarify?_ - Sorry, I'm totally confused and so is @Mat'sMug, I assume. — Gustav, Nov 15 '17 at 10:39

score 0 · Answer 1 · answered Nov 14 '17 at 22:39

You might want to see if the OLEDB provider supports paging. See this https://support.microsoft.com/en-us/help/202125/how-to-page-through-a-recordset-from-asp

The given link to an Access 97 database which suggest access does support this even though the code is an Active Server Page the PageSize property is still available to VBA clients.

Split Recordset Every 50 Records

1 Answers1