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

Jimmy the Hat

New Member
Joined
Feb 12, 2014
Messages
1
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:

Code:
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

 

Forum statistics

Threads
1,082,444
Messages
5,365,560
Members
400,838
Latest member
Renedescartes

Some videos you may like

This Week's Hot Topics

Top