Using DoEvents to stop Not-Responding

[bradyboyy88]

Hi,

I run a sub routine that does a series of ADO connections to a access database. It is querying and writing to the database. Because of this it is creating a not responding error due to the resources being used and I guess events building up. So I have been researching doevents and was curious what the forums thoughts were on this article of using sleep before using doevents alone?

Avoid Using DoEvents to Wait in Microsoft Access, VBA, and VB6

Anytime before I make a connection and query or write to the database I use doevents so I want to be more informed on the best way to use this.

Also would it be overkill to use doevents back to back before and after connection since I still get not responding most of the time due to the large amount of queries.

Doevents
Connection.Open QueryString
Doevents

 

[Norie]

Are you using multiple connections to the same database?

 

[bradyboyy88]

Are you using multiple connections to the same database?

I basically create my connection objects when opening the userform. Then for individaual tasks which are a result of clicking a button or what not, I open a single connection then do a bunch of queries and store the result in its respective disconnected recordset then close. I have doEvents at the beginning of each oConn.Open or oConn.Execute. I do about 17 queries or updates to the database in my subroutine and then I close the connection when done.


This is basically database connection tools I have been using and I have saved in a class and call whenever:

Option Explicit
'Database connection variables
Public oConn As ADODB.Connection
Public oRs As ADODB.Recordset
Public sConn As String


'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As ADODB.Recordset
       
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
    Set oRs = Nothing
    
End Function


Public Sub SQLWriteDatabase(SQLQuery As String)
    
    DoEvents
    
    'Open Record Set by executing SQL
    oConn.Execute SQLQuery


End Sub


Public Sub SQLCreateDatabaseConnection()
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient


End Sub


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=NO;ReadOnly=0;"";"
    
    End If
    
RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub




Public Sub SQLCloseDatabaseConnection()


    'Close Connection
    oConn.Close


End Sub


Public Sub SQLDestroyDatabaseConnection()


    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing


End Sub


Public Function SQLIsConnectionOpen()
    
    If oConn.State = 0 Then
        SQLIsConnectionOpen = False
    Else
        SQLIsConnectionOpen = True
    End If


End Function

 

[bradyboyy88]

Are you using multiple connections to the same database?

I basically create my connection objects when opening the userform. Then for when a user hits a button i execute my subroutine which opens the connection then does a bunch of queries where the results are stored in their respective disconnected recordset then I close the connection. I have doEvents at the beginning of each oConn.Open or oConn.Execute. I do about 17 queries or updates to the database in my subroutine before closing the connection. The access file sits on a network drive that is a bit slow which I was thinking could be the problem if there are no other methods of fixing this. I also keep it such that only one user can access the database at once to ensure data integrity, so if one user is in it then it tries to keep reconnect as you will see in the error handler. That is one reason I put the doevents since this error handler loop could go on for a few seconds.


This is basically database connection tools I have been using and I have saved in a class and call whenever:

Option Explicit
'Database connection variables
Public oConn As ADODB.Connection
Public oRs As ADODB.Recordset
Public sConn As String


'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As ADODB.Recordset
       
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
    Set oRs = Nothing
    
End Function


Public Sub SQLWriteDatabase(SQLQuery As String)
    
    DoEvents
    
    'Open Record Set by executing SQL
    oConn.Execute SQLQuery


End Sub


Public Sub SQLCreateDatabaseConnection()
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient


End Sub


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=NO;ReadOnly=0;"";"
    
    End If
    
RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub




Public Sub SQLCloseDatabaseConnection()


    'Close Connection
    oConn.Close


End Sub


Public Sub SQLDestroyDatabaseConnection()


    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing


End Sub


Public Function SQLIsConnectionOpen()
    
    If oConn.State = 0 Then
        SQLIsConnectionOpen = False
    Else
        SQLIsConnectionOpen = True
    End If


End Function

This is a sample routine of how my info is structured when using the connection tools

Sub Testing()


'Create database objects
DatabaseDirectory = "C:\bradyboyy88\filename.accdb"


Call DatabaseMethods.SQLCreateDatabaseConnection
          
DatabaseMethods.SQLOpenDatabaseConnection DatabaseDirectory, 0

SQLQueryCode = "Select blalh blah ablh"
Set ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode)

SQLQueryCode = "Select blalh blah ablh"
Set ActiveRecordset2 = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode)

SQLQueryCode = "Select blalh blah ablh"
Set ActiveRecordset3 = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode)

SQLQueryCode = "Select blalh blah ablh"
Set ActiveRecordset4 = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode)





DatabaseMethods.SQLCloseDatabaseConnection


DatabaseMethods.SQLDestroyDatabaseConnection


End Sub

 

[Jaafar Tribak]

Have you considered starting a new excel instance and running the code from there ? That should leave your current excel session free.

 

[bradyboyy88]

I was thinking about trying to jerry rig it that way. Some functions do need to wait though for others to finish though so it could get tricky.