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