Solved

Access Immediate Help - work Stop

Posted on 2014-07-21
12
435 Views
Last Modified: 2014-07-30
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?

R
queryIssue.png
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40210788
Can you show the VBA code you sue to copy. Include the lines where you use copyFromRecordset
0
 

Author Comment

by:Rayne
ID: 40210792
sure, here it is

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

Author Comment

by:Rayne
ID: 40210793
Dim objDB As DAO.Database
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 25

Expert Comment

by:chaau
ID: 40210796
How is myRng defined?
0
 

Author Comment

by:Rayne
ID: 40210805
Dim myRng As excel.Range
Const celAddr As String = "A1"
Set myRng = wks.Range(celAddr)
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 40210943
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.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 40210990
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.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 40211510
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

0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 250 total points
ID: 40211968
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:=";pwd=xyxyxyx")
   
   'Connect to database without password
   Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True)
   Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
   Set rng = Application.Sheets(1).Range("G1")
   rng.CopyFromRecordset rst
   
   rst.Close
   dbs.Close
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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, _
      CursorType:=adOpenForwardOnly
      
   '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
   rst.Close

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, _
                  ReadOnly:=False)
               appExcel.Visible = True
               wkb.Activate
               
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)
               rng.ClearContents
                              
               '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, _
                  Title:=strTitle

Open in new window


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

Expert Comment

by:Helen Feddema
ID: 40211975
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.
0
 

Author Closing Comment

by:Rayne
ID: 40228376
Thank you all for the the help.
Sorry for the delayed reply. There happened a family emergency.

Thanks all
0
 
LVL 85
ID: 40228765
Rayne: What was the fix for this? You've accepted every comment, it seems, and many of those have conflicting suggestions.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question