VBA to auto Refresh Linked Table Manager or linked tables

[aflores41]

Hello Everyone,

I was wondering if anyone knows of a way to add a code that would automatically refresh link tables upon opening of database. This is needed due to fact that sometimes split forms wouldn't open due to a lagg and would freak out the user with the error.

The fix was to refresh linked tables manually.

THanks./

-AF

[jforbes]

This might be what you are looking for: http://bytes.com/topic/access/insigh...bles-using-vba

I think I used NeoPa's example above to come up with this:

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

I run this from a Form that supplies the Database to relink to. It comes in handy during deployment to switch the Backend Database from a Development Database to a Production Database

[aflores41]

Thank you jforbes. Do I need that pwd=secret statement? I dont use password on my db.

Once again, thank you.

AF

[jforbes]

I'm not 100% sure what you would do for a blank password, probably include the parameter but pass a blank string, I think like this:

            tdf.Connect = "ODBC;Driver={SQL SERVER};" & "Server=DBSERVER\DB1;" & "Database=" & sDatabase & ";" & "Trusted_Connection=no;" & "Uid=sa;" & "Pwd="

For your reference, here are some other examples of a Connection Strings: https://www.connectionstrings.com/mi...r-odbc-driver/

[zmbd]

aflores41
if NOT using SQL server then the code J gave you can be simplified.

[jforbes]

Ah, nice catch Z. I've got some tunnel vision going on. =)

[aflores41]

Thanks. The code is not working for me though. I'm not linking to SQL Server just to the backend access data.

Any suggestions?

Thanks.

-AF

[Seth Schrock]

This article might be of some help, particularly post #2: Relinking Tables using VBA.

[jforbes]

I apologize for taking you down a path that you didn't need to travel. I primarily use Access as a Frontend and SQL as a Backend, so when you asked about linking, I forgot to keep in mind that there are multiple ways and reasons to link to a database.

You'll want to pretty much forget what I posted as it is skewed towards SQL.

I think this is what you are looking for: http://allenbrowne.com/ser-13.html I haven't used this code, but it looks like the right approach.

...Or the Article Seth has posted =)