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:

UPSDuder

New Member
Joined
Feb 14, 2019
Messages
10
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 ???
 

Forum statistics

Threads
1,085,721
Messages
5,385,459
Members
401,956
Latest member
MadameHugo

Some videos you may like

This Week's Hot Topics

Top