Results 1 to 3 of 3
  1. #1
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23

    Inporting from Access DAO, CopyFromRecordset not copying all data into Excel

    I am working with Excel and Access 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.

    I replace my Access Datadase daily as a third party modifies some of the content in the tables ,so I cannot build an export macro in vba. Therefore my solution is to pull this data from outside this Access File. I would be open to other suggestions as well.

    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:
    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
    .

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why can't you use a single line...
    Transferspreadsheet?

  3. #3
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    Quote Originally Posted by ranman256 View Post
    Why can't you use a single line...
    Transferspreadsheet?
    From my understanding, Transferspreadsheet will not work within excel, is it possible to run transferspreadsheet from a separate access ?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2017, 10:45 AM
  2. Excel copying over data
    By seamy20 in forum Forms
    Replies: 2
    Last Post: 12-22-2016, 08:41 AM
  3. Replies: 4
    Last Post: 07-14-2014, 02:26 PM
  4. Replies: 3
    Last Post: 03-12-2013, 11:34 AM
  5. Inporting Summary Sheets
    By Kapelluschsa in forum Import/Export Data
    Replies: 3
    Last Post: 08-13-2010, 02:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums