Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-08-2019, 10:32 AM   #16
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,312
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Quote:
Originally Posted by CJ_London View Post
sounds more and more like there is a problem with the sqland does it execute ok if you copy and paste it into a query?
Yes the SQL will run fine if I copy and paste it.

Quote:
what if you run the process two or three times for the same query? Or are you saying this is happening randomly for all queries
Yep, I can literally run the process multiple times in a row (the queries will be the same until the next day). The first few will work, then the last fails. Or it fails first time, then runs the next. There are between 15 and 40 queries for a given engineer and the failure can occur on any query.

I still think it's connected to something not finishing before the next thing starts but I can't pin it down further.

Alc is offline   Reply With Quote
Old 07-08-2019, 11:16 AM   #17
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,104
Thanks: 10
Thanked 221 Times in 209 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Access acting as if query is not present, just after telling me it is

Difficult to be sure when lengthy and nested code can't be tested, but here's what doesn't look right to ,me.
If this is false: If QueryExists(strSheetNameNew) Then
go here: GoTo Replace_Query
which says do this: Delete_Query (strSheetNameNew)

If it doesn't exist, how can you delete it?

Also, I question your placement of RstExport.MoveNext - you will only move if above is TRUE. If not, you are looping while on the same record.

RstExport.MoveNext
Else
Debug.Print strSheetNameNew & " does Not Exist"
GoTo Replace_Query
End If
<<<no move if false
Loop
You probably need to either move or add .Move just before the loop?
Also, it is usually considered best practice best to avoid GoTo to control code direction.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Alc (07-09-2019)
Old 07-08-2019, 01:24 PM   #18
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

@micron: I made the same point in post #8 but the OP is 100% confident the issue is with the transferspreadsheet line.

Since introducing doevents to slow things down did not resolve the issue I believe that due to the randomness of the error the issue is either with the data or the looping. But unable to get a clear confirmation that this has been tested thoroughly or the code has been stepped through to check variable values at the time of failure.

@Alc - suggest you research and apply debug.assert to stop the code when about to fail (i.e. before the Debug.Print strSheetNameNew & " does Not Exist" line and thoroughly check all the variables and step through the code at that point.

just in case it is actually the speed of operation causing the issue I would add further doevents between each line of code and see if that resolves the issue

then repeat same query, same data enough times to be sure the problem is solved.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2019, 09:06 PM   #19
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Access acting as if query is not present, just after telling me it is

Try in the error handler to resume to the same code line, remember to put a breakpoint in that code line where the Resume is, then if it is a time issue it will execute.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 07-08-2019, 09:08 PM   #20
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Access acting as if query is not present, just after telling me it is

as suggested, add a pause period on the code:
Code:
Replace_Query:

            Delete_Query (strSheetNameNew)
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
            WaitSeconds 10
            
            If QueryExists(strSheetNameNew) Then
                'Debug.Print strSheetNameNew & " exists"
                
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
                WaitSeconds 10
                DoCmd.DeleteObject acQuery, strSheetNameNew
on a Module:
Code:
Option Compare Database
Option Explicit

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#End If

Public Function WaitSeconds(intSeconds As Integer)
  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds      Number of seconds to wait
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Dim datTime As Date

  datTime = DateAdd("s", intSeconds, Now)

  Do
   ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datTime

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
  Resume PROC_EXIT
End Function
try adjusting if the waiting time is too long/short.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-09-2019, 03:16 AM   #21
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,312
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Thanks for the feedback.
Quote:
Originally Posted by Micron View Post
Difficult to be sure when lengthy and nested code can't be tested, but here's what doesn't look right to ,me.
If this is false: If QueryExists(strSheetNameNew) Then
go here: GoTo Replace_Query
which says do this: Delete_Query (strSheetNameNew)

If it doesn't exist, how can you delete it?
I was trying to get something to happen. Each time the code fails, I can see that the query does exist so I was experimenting to find out what would happen if it tried to delete it again.
Quote:
Also, I question your placement of RstExport.MoveNext - you will only move if above is TRUE. If not, you are looping while on the same record.
The one was a mistake. The move next used to be outside the if statement but I moved it when I added debugging. I'd have realized my screw up if the damn thing wasn't failing before hitting that part, as I'd only get one file produced.

As far as the GoTo thing is concerned, I rarely use it but though it was the easiest way to accomplish my test.

Quote:
Originally Posted by CJ_London View Post
@Alc - suggest you research and apply debug.assert to stop the code when about to fail (i.e. before the Debug.Print strSheetNameNew & " does Not Exist" line and thoroughly check all the variables and step through the code at that point.

just in case it is actually the speed of operation causing the issue I would add further doevents between each line of code and see if that resolves the issue

then repeat same query, same data enough times to be sure the problem is solved.
Will work through this, this morning. It's causing problems, so has become my top priority and I don't have to worry about getting sidetracked.

Thanks again.
Alc is offline   Reply With Quote
Old 07-09-2019, 09:19 AM   #22
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,312
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Finally, it appears to be working.

The solution was to add a short delay (using a new sub procedure) in between the part that creates the query and refreshes the database and the part that checks if the query exists before exporting its contents. Changes shown in red.

I tried with a long pause of 10 seconds first, just to see if that worked. Once it did, I cut it down by increments until I got to the lowest value that produced no errors when tested on any of the PCs that might need to run the procedure. Just to be safe, I then added one second.

Thanks to all for your help.


Code:
Public Function Export_Data(strNewBook As String, strSheetName As String, Db As Database, strSQLToRun As String)
    Dim QdfNew As QueryDef
    Dim xlSheetToFormat As Object
    Dim RstExport As Recordset
    Dim strNewSQL As String
    Dim strSheetNameBase As String
    Dim strSheetNameNew As String
    Dim strPrefix As String
    Dim strPartNo As String
    Dim strYear As String
    
    ' this is the collection
    Dim Coll As New Collection
    
    On Error GoTo Err_Point

    strSheetNameBase = strSheetName

    Set RstExport = Db.OpenRecordset(strSQLToRun)
    If RstExport.RecordCount <> 0 Then
        RstExport.MoveFirst
        Do While Not RstExport.EOF
            strNewSQL = Db.QueryDefs("qryexceedancemgmt(MV)_MultiExport2").SQL
            
            strPrefix = Left(RstExport![DataTable], Len(RstExport![DataTable]) - 1)
            strYear = Right(RstExport![DataTable], 1)
            strPartNo = RstExport![Part#]
            
            strNewSQL = Replace(strNewSQL, "AAAAA", strPrefix)
            strNewSQL = Replace(strNewSQL, "BBBBB", strYear)
            strNewSQL = Replace(strNewSQL, "CCCCC", strPartNo)

            strSheetNameNew = strSheetNameBase & "_" & strPartNo & "_" & strPrefix & strYear

Replace_Query:

            Delete_Query (strSheetNameNew)

            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh

            Application.RefreshDatabaseWindow
            
            WaitFor (3)
            
            If QueryExists(strSheetNameNew) Then
                'Debug.Print strSheetNameNew & " exists"

                ' add the query name to collection object
                Coll.Add strSheetNameNew

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
                                
                Set QdfNew = Nothing
                             
            Else
                Debug.Print strSheetNameNew & " does Not Exist"
                
                GoTo Replace_Query
            End If
            
            RstExport.MoveNext

        Loop
    End If
    
Exit_Point:
    ' delete the queries made
    Dim i As Integer
    For i = 1 To Coll.Count
        CurrentDb.QueryDefs.Delete Coll.Item(i)
    Next
    Set Coll = Nothing
    
    Exit Function
    
Err_Point:
    
    strResponse = MsgBox(Err.Number & Chr(13) & Err.DESCRIPTION, vbCritical, "Error")
    Resume Exit_Point
End Function

Sub WaitFor(NumOfSeconds As Long)
    Dim SngSec As Long
    
    SngSec = Timer + NumOfSeconds
    
    Do While Timer < SngSec
    DoEvents
    Loop
End Sub


Alc is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
check if the values present in column A of excelsheet are also present in Access tabl aman Modules & VBA 1 09-03-2014 09:11 AM
Don't need a heading telling me it's a sum JordanR Queries 2 09-17-2008 07:07 AM
Find records that are present in one table, but are not present in other tables. mattcdse Queries 6 06-01-2007 04:11 AM
Access acting weird DomZ General 7 08-06-2003 06:31 PM
Telling a query to return last 3 rows durdle Queries 4 07-18-2003 12:34 PM




All times are GMT -8. The time now is 07:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World