Returning a specific number of records

[exRP12Nuke]

Hello,

I am using Access 2010, and I have a table that contains over 2 million records in it. I need to break that table down into a more manageable size. What I am looking to do is to have a query that returns 24999 records, another query that returns the next 24999 records, so on and so forth through the end of the large table. So I am going to need roughly 90 individual queries that I can export to *.csv. Is there an easy way to do this? I am sure the VBA will be involved, but I am just unsure of how to build it.

Any help would be greatly appreciated, thank you.

 

[MajP]

Change the constants to match your table, primary key field, the export path, and the batch size.

Public Sub TestExportGroups()
  Const batchSize = 100
  Const tableName = "Orders"
  Const PK_fldName = "OrderID"
  Const ExportPath = "C:\"
  Call ExportGroups(batchSize, tableName, PK_fldName, ExportPath)
End Sub

Public Sub ExportGroups(batchSize As Long, tableName As String, PK_fldName As String, ExportPath As String)
  Dim strSql As String
  Dim I As Long
  Dim counter As Long
  Dim TotalRecords As Long
  Dim UsedRecords As Long
  TotalRecords = DCount(PK_fldName, tableName)
  For I = batchSize To TotalRecords Step batchSize
   UsedRecords = counter * batchSize
   strSql = "SELECT TOP " & I & " * from " & tableName
   If Not counter = 0 Then
     strSql = strSql & " Where " & PK_fldName & " NOT IN (SELECT TOP " & UsedRecords & " " & PK_fldName & " from " & tableName & ")"
   End If
   counter = counter + 1
   Debug.Print strSql
   CreateAndExport strSql, "Orders" & I, ExportPath
  Next I
End Sub

Public Sub CreateAndExport(strSql As String, qryName As String, ExportPath As String)
  Dim qdf As QueryDef
  CurrentDb.CreateQueryDef qryName, strSql
  CurrentDb.QueryDefs.Refresh
  DoCmd.TransferText acExportDelim, , qryName, ExportPath & qryName & ".txt", True
  CurrentDb.QueryDefs.Delete (qryName)
End Sub

The key is that this makes querys like this

SELECT TOP 100 * from Orders
SELECT TOP 200 * from Orders Where OrderID NOT IN (SELECT TOP 100 OrderID from Orders)
SELECT TOP 300 * from Orders Where OrderID NOT IN (SELECT TOP 200 OrderID from Orders)
SELECT TOP 400 * from Orders Where OrderID NOT IN (SELECT TOP 300 OrderID from Orders)
SELECT TOP 500 * from Orders Where OrderID NOT IN (SELECT TOP 400 OrderID from Orders)
SELECT TOP 600 * from Orders Where OrderID NOT IN (SELECT TOP 500 OrderID from Orders)
SELECT TOP 700 * from Orders Where OrderID NOT IN (SELECT TOP 600 OrderID from Orders)
SELECT TOP 800 * from Orders Where OrderID NOT IN (SELECT TOP 700 OrderID from Orders)

Saves those queries as querydefs
exports as text files
removes the querydefs