Inporting from Access DAO, CopyFromRecordset not copying all data

UPSDuder

New Member
Joined
Feb 14, 2019
Messages
10
I am woking with Excel 2013, and I am trying to import all rows and columns of data from a Select Query in Access. I have 4695 rows of data within the access query and Excel is only importing 1457 rows. I'm quite confused as to how its only partially working.

If I am to step into the code while it is running, and hover my curser over "rs.RecordCount" it does return 4695, so I know it has found all of the data.

Possible reference Issue?
References:
Visual Basic for applications
Microsoft Excel 15 object library
OLE Automation
Microsoft Office 15 Object library
Microsoft Access 15 Object library
Microsoft Forms Object Library
Microsoft Office 15 Acces database engine Object library


Code:
    Const DbLoc As String = "C:\Users\mcz0pjy\Downloads\UPSPickupPointTracker.accdb"
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim recCount As Long
    Dim SQL As String
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim LastRow As Long
    
    'Setup references to workbook and sheet
    Set xlBook = ActiveWorkbook
    Set xlSheet = xlBook.Sheets("qry IE Report Base")
    LastRow = xlSheet.Range("I" & Rows.Count).End(xlUp).Row
  


'Communicate with the user
    Application.StatusBar = "Connecting to an external database..."
    Application.Cursor = xlWait
 
    Set db = OpenDatabase(DbLoc)
   
    Set rs = db.OpenRecordset("qry IE Report Base", dbOpenSnapshot)
    
    'Copy recordset to spreadsheet
    Application.StatusBar = "Writing to spreadsheet..."
   If rs.RecordCount = 0 Then
        MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No Data"
        
    Else
        rs.MoveLast
       recCount = rs.RecordCount
        rs.MoveFirst
        
   End If
   
  xlSheet.Range("A2").CopyFromRecordset rs
    
   db.Close
 
Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
PS - does anyone else find it extremely hard to type on this forum, as it seems to freeze and not recognize all of my key strokes ???
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top