Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

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?

  • 4
  • 2
  • 2
  • +3
4 Solutions
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now