Access Immediate Help - work Stop

Hello All,

There is a query in access that I use in VBA. So I use copyFromRecordset to pull in the query results of access query into a table in excel. I discovered that the query will show exactly 1000 rows when I view in access but when I pull the query results in to excel using copyFromRecordset, it pulls only 850 rows. Now to further investigate, I tried exporting the query results by right clicking and export with no formatting into excel and then I get this notification. See image.
So what’s happening with the query? Why certain rows are missed?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you show the VBA code you sue to copy. Include the lines where you use copyFromRecordset
RayneAuthor Commented:
sure, here it is

Const strcQueryName As String = "qryMain"
Set qryMainPLG = objDB.QueryDefs(strcQueryName)
Set objRS = qryMainPLG.OpenRecordset
myRng.Offset(1, 0).CopyFromRecordset objRS
RayneAuthor Commented:
Dim objDB As DAO.Database
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

How is myRng defined?
RayneAuthor Commented:
Dim myRng As excel.Range
Const celAddr As String = "A1"
Set myRng = wks.Range(celAddr)
Dale FyeCommented:
Only thing I can think of would be a value in one of the cells that is violating some sort of rule in Excel. Maybe one of the values starts with an '=' and Excel is trying to interpret it as a formula, and generating an error.

Have you taken a look at the 849th - 852nd records in the Access query grid to see if there is an error in one of the column values in those records.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might also create a query that includes ONLY those records you think are giving trouble, and use that query. Often this will give you a better idea of what's going on.
Please try this.  I eliminate the query object as an intermediary.  Assume that your objDB is a proper reference to the database.  I do not know if it is a connection object or something else.
Dim objRS As Object
Set objRS = CreateObject("ADODB.Recordset")

Const strcQueryName As String = "qryMain"
Set objRS = objDB.OpenRecordset(strcQueryName, dbOpenDynaset)
myRng.Offset(1, 0).CopyFromRecordset objRS

Open in new window

Helen FeddemaCommented:
Here are several variations of code for using copyFromRecordset to insert records from an Access query into an  Excel workbook:
Excel VBA:
Public Sub GetDataFromAccessDAO()
'Created by Helen Feddema 23-Feb-2010
'Last modified by Helen Feddema 23-Feb-2010

On Error GoTo ErrorHandler

   Dim strDatabaseNameAndPath As String
   Dim strRecordSource As String
   Dim dbe As DAO.DBEngine
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lngCount As Long
   Dim rng As Excel.Range
   Dim strPrompt As String
   Dim strTitle As String
   Dim strSearch As String
   Dim strLastName As String
   strRecordSource = "qryNewContacts"
   strDatabaseNameAndPath = "G:\Documents\ExpertsExchange\ExpertsExchange Sample Code.mdb"
   Set dbe = DAO.DBEngine
   'Connect to database with password
   'Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True, _
   'Connect to database without password
   Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
   Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
   Set rng = Application.Sheets(1).Range("G1")
   rng.CopyFromRecordset rst
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in GetDataFromAccessDAO procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Access VBA using ADO:
   Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
   'Export query
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst

Open in new window

Access VBA using DAO:
On Error Resume Next
               Set appExcel = GetObject(, "Excel.Application")
               Set wkbTest = appExcel.Workbooks(strXLFileName)
               If Err.Number = 0 Then
                  'Workbook is already open
                  wkbTest.Close savechanges:=xlDoNotSaveChanges
                  Set wkbTest = Nothing
               End If
               Set wkb = appExcel.Workbooks.Open(FileName:=strXLFile, _
               appExcel.Visible = True
On Error GoTo ErrorHandler
               Set sht = wkb.Sheets("Access Data")
               'Clear old data, if any
               lngLastRow = sht.UsedRange.Rows.Count + 2
               strRange = "A3:Q" & CStr(lngLastRow)
               Set rng = sht.Range(strRange)
               'Create a DAO recordset
               Set rng = sht.Range("A3")
               Set rstDAO = CurrentDb.OpenRecordset(strRecordSource)
               rng.CopyFromRecordset rstDAO
               strTitle = "Export successful"
               strPrompt = strXLFile & " filled with current Access data"
               MsgBox prompt:=strPrompt, _
                  Buttons:=vbInformation + vbOKOnly, _

Open in new window

(The last code segment is from the sample database for my Working with Excel ebook).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
If you have problems when exporting the query, try using a make-table query to create a temp table, and export the table instead.  If there are any problems, they may appear when creating the table in Access, which should be easier to debug.
RayneAuthor Commented:
Thank you all for the the help.
Sorry for the delayed reply. There happened a family emergency.

Thanks all
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Rayne: What was the fix for this? You've accepted every comment, it seems, and many of those have conflicting suggestions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.