VBA fails to update a linked table - works on step through / with timer / with manual refreshall

[Jimmy the Hat]

Hi

I have a table in Access linked to 3 separate Excel sheets. They contain user forms which update the records in the table with information input into the form. Within the userform code, I'd like to do 2 things:

1. Update the records in the datatable with the user form info
2. Refresh the linked table, to make Excel report reflect the information input in 1.

The problem I'm having is that the linked table will only refresh on EVEN attempts. I.e. attempt one - linked table does not show the new data; attempt two - linked table reflects the changes for both attempt 1 and attempt two.

If I step through the code, it works!!

If I put in a 5 second wait before the refresh, it works!!

I have disabled background refresh in the linked tables and also changed the Standard ADO Connection Object Initialization mode property to Share Deny None (so all 3 Excel sheets can read / write at the same time)

Help - I'm tearing my hair out!

Code looks like this:

Private Sub CommandButton1_Click()

Application.Calculation = xlManual
Application.ScreenUpdating = False
[INDENT]
If datavalidation Then[/INDENT]
[INDENT]'datavalidation is a function that checks the presence of sufficient data on the form - works fine[/INDENT]
        
        'Update the Access Database
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim stDB As String, stSQL As String, apptSQL As String, stProvider As String
          
        stDB = "Data Source= " & ThisWorkbook.Path & "\CAS.accdb"
        stProvider = "Microsoft.ACE.OLEDB.12.0"
        
          'Opening connection to database
          With cn
              .ConnectionString = stDB
              .Provider = stProvider
              .Open
    
          End With
           
        'Update appt status & record - status marked
        Dim appt As String
        [INDENT]appt = ComboBox4.Value[/INDENT]
               
        Select Case appt
        [INDENT]'Create some Update SQL called apptSQL text based on a series of different criteria - this all works fine[/INDENT]
[INDENT]Case 1
Case 2
Case 3[/INDENT]


        End Select
        
        cn.Execute apptSQL
               
        cn.Close
        Set rs = Nothing
        Set cn = Nothing


        Unload Me
    
    'Application.Wait (Now + TimeValue("0:00:05")) > this works!!
    
    'refresh table - this refresh only works sometimes!!
    Dim Oconn As OLEDBConnection
    Dim Wconn As WorkbookConnection
        
        For Each Wconn In ThisWorkbook.Connections
            Set Oconn = Wconn.OLEDBConnection
            Oconn.BackgroundQuery = False
            Oconn.Refresh
                Do While Oconn.Refreshing
                    DoEvents
                Loop
        Next Wconn


    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

 

[Randombard]

I had a similar problem with a table, in the end it came down to if Access was still connected to that table.

I expect that is why the wait of 5 seconds after the unload is working, you might want to try something like:

VBA Tips & Tricks: Generic Function to Check if Application is Running