Solved Auto Refresh Linked Table sourced from Excel (1 Viewer)

PatAccess

Registered User.
Local time
Today, 13:34
Joined
May 24, 2017
Messages
260
Hello Guys,
I want to refresh a linked table sourced from Excel. I found the following code online
Code:
Public Function RefreshLinks(ByVal sDatabase As String) As Boolean
On Error GoTo ErrorOut
 
    'Refresh table links to a backend database
 
    Dim dbs As Database
    Dim tdf As TableDef
    Dim sCurrentTDF As String
 
    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = "ODBC;Driver={SQL SERVER};" & "Server=DBSERVER\DB1;" & "Database=" & sDatabase & ";" & "Trusted_Connection=no;" & "Uid=sa;" & "Pwd=secret"
            Err = 0
            On Error Resume Next
            sCurrentTDF = tdf.Name
            tdf.RefreshLink ' Relink the table.
            If Err <> 0 Then
                RefreshLinks = False
                Exit Function
            End If
        End If
    Next tdf
 
    RefreshLinks = True
ExitOut:
    Exit Function
ErrorOut:
     msgBox ("There was an error refreshing the link(s) for '" & sCurrentTDF & "':  " & vbCrLf & vbCrLf & Err.Description)
     Resume ExitOut
End Function
But as you can see this is for ODBC Drivers so like a SQL Server, what would be the equivalent for Excel linked tables please?

Thank you,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:34
Joined
Oct 29, 2018
Messages
17,158
Hi. It might look something similar to this.

Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\FolderName\ExcelFile.xlsx
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:34
Joined
Feb 19, 2002
Messages
33,755
Each time you open the database, the link will refresh. To refresh while the db is open, delete the link and use the TransferSpreadsheet to relink.
 

bastanu

AWF VIP
Local time
Today, 10:34
Joined
Apr 13, 2010
Messages
902
If the connection string stay the same you don't need to specify it in the loop. So just comment out or remove the line (tdf.Connect = "ODBC... and all linked tables in the db will get refreshed.

Cheers,
 

PatAccess

Registered User.
Local time
Today, 13:34
Joined
May 24, 2017
Messages
260
Each time you open the database, the link will refresh. To refresh while the db is open, delete the link and use the TransferSpreadsheet to relink.
I just tested this out and it works. I didn't know about the TransferSpreadsheet method and just read about it as well. Thank you for your help
 

PatAccess

Registered User.
Local time
Today, 13:34
Joined
May 24, 2017
Messages
260
If the connection string stay the same you don't need to specify it in the loop. So just comment out or remove the line (tdf.Connect = "ODBC... and all linked tables in the db will get refreshed.

Cheers,
Got it! This option also works
 

PatAccess

Registered User.
Local time
Today, 13:34
Joined
May 24, 2017
Messages
260
Thank you very much Guys. All of these options work great!

Quick question. Is there a similar forum for SQL Server and PostgreSQL programming. This MS Access VBA forum has been indispensable to me and I am looking for something similar for the other ones
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:34
Joined
Feb 19, 2002
Messages
33,755
This forum has a SQL Server section. Some of the same people post there.
 

PatAccess

Registered User.
Local time
Today, 13:34
Joined
May 24, 2017
Messages
260
Oh. I didn't know that. I'll be exploring it today. Thanks so much Guys
 

Users who are viewing this thread

Top Bottom