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

Jimmy the Hat

New Member
Feb 12, 2014

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
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
          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
        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
                Do While Oconn.Refreshing
        Next Wconn

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


Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Latest member