Solved

Access Immediate Help - work Stop

Posted on 2014-07-21
12
434 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 47

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 84

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

738 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