MS Access-CopyFromRecordset

Question

I am using below code to fetch records from a db. I have more than 5,000,000 records. The below code pulls 1048576 records and pastes in Sheet 2. Can someone help me to loop it so that it pulls all records and places it from sheet1 than sheet2 than sheet3 until all records are pasted.

Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Sheet2"
Const conWKB_NAME = "\\workbook path\a\b\c\Work.xlsm"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("Database", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
      .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
          intLastCol)).ClearContents
      .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = True
      .Range("A2").CopyFromRecordset rs
    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

Why do you need 5m records in a single file? I'm guessing the file will take *forever* to save, and take even longer to load (if it loads at all). Excel might allow 1m rows, but it's not designed to be abused. — ThunderFrame, Jun 27 '17 at 11:59
Whatever the reason you think you need 5 million rows in Excel is, it's wrong. No Excel worksheet **ever** needs to work with that much data. You're going to be aggregating it, summarizing it, looking things up, etc. - make the SQL backend do that work, and only give Excel the data it *needs*. This is a blatant X-Y problem: you're solving a symptom (too much data to fit worksheet) rather than the actual problem at hand (wild guess, you're making some report), making Excel do the job of a database. — Mathieu Guindon, Jun 27 '17 at 15:07

Nathan_Sav · Accepted Answer · 2017-06-28T13:22:34.073

0

Not a full answer, as not sure of the structure of the table, but using a table with a primary key, I did the following, you'll need to do a count of the records and set the loop up according to that, but something along these lines

Sub test()

    Dim strsql As String
    Dim l As Long
    Dim x As Long  ' x will be recordcount/ l

    l = 10000   ' max rows

    For x = 1 To 3
        strsql = "select top " & l & " y.* from (" & _
                "Select top " & (x * l) & " * from [Table] order by [ID] desc" & _
                ") as Y order by y.id asc"
        Debug.Print strsql 
    Next x

    End Sub

This generates SQL like so

select top 10000 y.* from (Select top 10000 * from [Table] order by [ID] desc) as Y order by y.id asc
select top 10000 y.* from (Select top 20000 * from [Table] order by [ID] desc) as Y order by y.id asc
select top 10000 y.* from (Select top 30000 * from [Table] order by [ID] desc) as Y order by y.id asc

EDIT

Sub test()

    Dim strsql As String
    Dim l As Long
    Dim x As Long  ' x will be recordcount/ l
    dim rst as adodb.recordset

    l = 10000

    For x = 1 To (dcount("id","table")/l)
        strsql = "select top " & l & " y.* from (" & _
                "Select top " & (x * l) & " * from [Table] order by [ID] desc" & _
                ") as Y order by y.id asc"
    set rst=new adodb.recordset
    rst.open strSQL, currentproject.connection, adOpenKeySet
        worksheets(x).range("a1").copyfromrecordset rst
    Next x

End Sub

Hope this helps

edited Jun 28 '17 at 13:22

answered Jun 27 '17 at 11:43

Nathan_Sav

8,466
2
13
20

Won't that just get you the same top 10000 records each time? I believe `TOP` pulls in PK order, so all you're doing is getting the same 10,000 records from increasingly larger sub-sets of `[Table]`. – FreeMan Jun 27 '17 at 12:27
My bad. You won't get the same 10,000 rows because of PK ordering. You'll select ever larger, distinct subsets of `Table` based on `Order by ID`, but there's [no telling](https://stackoverflow.com/questions/824855/does-select-always-order-by-primary-key) which 10,000 rows of those subsets you _will_ get. – FreeMan Jun 27 '17 at 12:33
nope, see the edit for image. It relies on a sortable field, as eluded to in my answer, but o/p shared no data. There's the recordset move and loop method, but not the best either. – Nathan_Sav Jun 27 '17 at 12:38
1

As written, my comments apply. As written in your screen shots, you are correct. Your code is missing the extra `order by y.id` shown in the screen shots. – FreeMan Jun 27 '17 at 12:58
Hi Nathan, I am getting error at rst.open strSQL, currentproject.connection, adOpenKeySet. the select statement includes a reseverd word, or the punctuation is incorrect.or th argument name is incorrect or missing. kindly help correcting it – Apurv Pawar Jun 28 '17 at 13:56
what is your sql? I cant solve issues for you each time, you need to investigate, I cant see your screen nor your data, so have a look at the generated SQL and see where the error might be. – Nathan_Sav Jun 28 '17 at 14:04

score 0 · Answer 2 · answered Jun 28 '17 at 04:38

0

That will NOT work in Excel, now, or probably ever in our lifetimes. Consider using SQL Server express, which is free, or R, which is also free.

answered Jun 28 '17 at 04:38

ASH

20,759
19
87
200

MS Access-CopyFromRecordset

2 Answers2

Linked