Solved

Access Immediate Help - work Stop

Posted on 2014-07-21
12
422 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
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 24

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
 
LVL 24

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Outlook Free & Paid Tools
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now