none
Suppress windows "No response" message during ADODB.Recordset.Open RRS feed

Answers

  • I'm surprised LockWindowUpdate didn't work though it's not a solution I would want to use, if anything goes wrong it's difficult to unlock.

    What I did was to include a little message (on the form) for user to ignore a temporary "not responding" caption.

    • Marked as answer by Andreas Killer Friday, September 12, 2014 7:04 AM
    Thursday, September 11, 2014 11:13 AM
    Moderator

All replies

  • Have you tried disabling ApplicationAlerts under On Error Resume next, then If Err.number then ... ?

    In passing, in the file you uploaded the values in your Arbeitsplan are formatted as text which I assume explains why

    Arbeitsplan = 100 fails, but changing to Arbeitsplan = '100' works

    However 100 (without apostrophes) worked fine after I formatted the cells as numbers

    Monday, September 8, 2014 12:38 PM
    Moderator
  • Arbeitsplan = 100 fails, but changing to Arbeitsplan = '100' works

    You have misunderstood me, my code exports the data correctly. The SQL string is just a sample from my test routine.

    BTW, in the interim I've changed the SQL string and I use the LIKE operator, see code below.

    The issue is that rsData.Open needs so much time that windows throws the "No response" message and immediately after that my phone rings and the end user shouts me "your program has crashed".

    I really want to prevent that. :-)

    Andreas.

    Option Explicit
    Option Private Module
    
    Private Property Get ConnectionString(ByVal Filename As String) As String
      'Build a connection string for a file
      Const Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""@FILE"";Extended Properties=Excel 12.0"
      ConnectionString = Replace(Provider, "@FILE", Filename)
    End Property
    
    Private Function MakeSQLWildcards(ByVal S As String) As String
      'Wildcards:
      '%   A substitute for zero or more characters
      '_   A substitute for a single character
      '[charlist]  Sets and ranges of characters to match
      '[^charlist]
      'or
      '[!charlist]   Matches only a character NOT specified within the brackets
      S = Replace(S, "*", "%")
      S = Replace(S, "?", "_")
      MakeSQLWildcards = S
    End Function
    
    Sub Import_AP(ByVal Nummer As String, ByVal Arbeitsplan As String, ByVal Filename As String)
    Dim objConnection As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim Arr, Header
    Dim R As Range

    ' SetTimer Application.hwnd, 100, 100, AddressOf TimerProc
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open ConnectionString(Filename)

    Application.Cursor = xlWait
    Set rsData = New ADODB.Recordset
    rsData.Open "SELECT * FROM [Arbeitspläne$] WHERE (Fertigungsartikel LIKE '" & MakeSQLWildcards(Nummer) & "') AND (Arbeitsplan LIKE '" & MakeSQLWildcards(Arbeitsplan) & "')", objConnection
    If rsData.EOF Then
    MsgBox "Artikelnummer '" & Nummer & "' Arbeitsplan '" & Arbeitsplan & "' nicht gefunden"
    GoTo ExitPoint
    End If

    Header = RecordSet_GetHeader(rsData)
    Arr = rsData.GetRows
    Arr = Transpose(Arr)

    Set R = Cells.Find("*", SearchDirection:=xlPrevious)
    If R Is Nothing Then
    Set R = Range("A1")
    Else
    Set R = R.Offset(2, 1 - R.Column)
    End If

    R.Resize(, UBound(Header)) = Header
    With R.Offset(1).Resize(UBound(Arr) + 1, UBound(Arr, 2) + 1)
    .ClearFormats
    .NumberFormat = "@"
    .Value = Arr
    .EntireColumn.ColumnWidth = 200
    .EntireColumn.AutoFit
    .EntireRow.AutoFit
    End With
    R.Resize(UBound(Arr) + 2, UBound(Arr, 2) + 1).Select

    ExitPoint:
    rsData.Close
    objConnection.Close
    ' KillTimer Application.hwnd, 100
    Application.Cursor = xlDefault
    End Sub
    Tuesday, September 9, 2014 5:42 AM
  • RE...and immediately after that my phone rings and the end user shouts me "your program has crashed". I really want to prevent that. :-)

    Don't answer the phone!

    Or, would this help

    ' MsgBox objConnection.CommandTimeout ' default = 30
    objConnection.CommandTimeout = 120

    CommandTimeout Property (ADO)

    Tuesday, September 9, 2014 3:05 PM
    Moderator
  • Or, would this help
    ' MsgBox objConnection.CommandTimeout ' default = 30
    objConnection.CommandTimeout = 120

    Unfortunately, no. I have added the line after

    objConnection.Open ConnectionString(Filename)

    When I open my form, enter some data, click the button and after (around) 4 or 5 seconds I get the "no response":

    When Open has finished, the rest of my code needs around 0,3 seconds to write the data into the sheet and the message goes away:

    Any other ideas?

    Andreas.

    Wednesday, September 10, 2014 5:10 AM
  • Sorry I didn't read your OP properly, even after you tried to explain second time, in particular about the "no response" appearing on your userform's caption, 

    I don't recall seeing that message appear on a userform but frequently see it on Excel's main window caption while code is working, in English it reads "... Not Responding", also sometimes the window appears to "fade", then recover.

    I first noticed this in Vista but still same in Win7 though less frequent (maybe different machine specs). It is very disconcerting to users who think the program has crashed when in fact it might be normal behaviour. I made many comments about it at the time but never seen a way to prevent the message appearing, at least on Excel's caption.

    Intuitively I think there ought to be a way to prevent the message appearing on a userform but can't test your scenario.

    Wednesday, September 10, 2014 8:43 AM
    Moderator
  • I first noticed this in Vista but still same in Win7 though less frequent (maybe different machine specs). It is very disconcerting to users who think the program has crashed when in fact it might be normal behaviour. I made many comments about it at the time but never seen a way to prevent the message appearing, at least on Excel's caption.

    I already tried the API LockWindowUpdate but that did not affect the title bar of the window.

    I'm on vacation the next weeks, when I'm back I'll try a crude method: Make a screen shot of the window, install a timer routine and paint the screen shot back. That is a little bit crazy... I'll give you a sign if and how good it works.

    Andreas.

    Thursday, September 11, 2014 7:17 AM
  • I'm surprised LockWindowUpdate didn't work though it's not a solution I would want to use, if anything goes wrong it's difficult to unlock.

    What I did was to include a little message (on the form) for user to ignore a temporary "not responding" caption.

    • Marked as answer by Andreas Killer Friday, September 12, 2014 7:04 AM
    Thursday, September 11, 2014 11:13 AM
    Moderator
  • What I did was to include a little message (on the form) for user to ignore a temporary "not responding" caption.

    You're right, the simplest solution is the best, I'll do it that way.

    Andreas.

    Friday, September 12, 2014 7:04 AM