Refreshing Tables Connected to SQL Database

[DBAMJA]

I have an Access 2010 front end connected to an MS Sql database as the back end. I have had to make some changes to the back end to a couple of the tables and need to programmatically refresh the front end of the database. That's easy enough to do manually on my development database but the production front end has been distributed to roughly 65 desktops and I don't want to have to go to each and every desktop to manually refresh the linked tables. Any ideas on how to refresh the linked tables without any intervention from the user?

Thanks in advance for any assistance you may provide.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[dhookom]

Have you considered a method for automatically pushing out a new application? Otherwise you would need some common tables that store version information. If the local version table doesn't match the shared version table, you would run some DAO code to refresh the linked tables.

There are lots of examples for refreshing links. I typically use Doug Steele's DSN-less code.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

I only have @ 30 users, but they are a mishmash of in-house, remote UK based and remote in various countries around the globe.

The way i do this is as follows...

1. I have code in my app that re-links all tables from my SQL server, which is ran in the devel source code version and then the app is compiled to an ACCDR.

2. I have a global constant that stores the app current version number.

3. I have a table in SQL that stores the app name and current version.

4. I have a function that runs at various stages of user interaction with the app to check the internal version number constant with the SQL DB number, if they don't match, the user get a popup message informing them the app will now close and update itself, once acknowledged by the user, the app closes and runs the update script.

5. I have a DOS batch file that copies the new app version from the corporate network folder share where I put all latest compiled ACCDR version of our in-house apps and overwrites the users local install, then re-opens the application. The script can also copy other files should I need it such as a change to a merge template or other resource that needs updating.

The re-link code is similar to what Duane posted and it was probably he who turned me onto it many moons ago...

I use a prefix on tables to distinguish SQL server location as well as databases as we have several along with differing user credentials.

I use alternative methods for change logging / user tracking as windows trusted authentication is not an option for SQL access due to some SQL servers being in the cloud and not on the corporate domain.

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
    
    Dim sServer As String
    Dim sPWord As String
    Dim sUser As String
    
    
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
        'MsgBox "connect : " & .Connect
            'split current string to find db data
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        If left(.Name, 4) = "dbo_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                            sServer = "127.0.0.1, 1433"
                            sPWord = "MyPWD"
                            sUser = "MyUID"
                        ElseIf left(.Name, 4) = "rem_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                            sServer = "127.0.0.1, 1433"
                            sPWord = "MyPWD"
                            sUser = "MyUID"
                        ElseIf left(.Name, 4) = "cld_" Then
                            sServer = "127.0.0.2, 1433"
                            sPWord = "MyPWD"
                            sName = Right(.Name, Len(.Name) - 3)
                            sUser = "MyUID"
                        Else
                            sName = .Name
                            sServer = "127.0.0.3, 1433"
                            sPWord = "MyPWD"
                            sUser = "MyUID"
                        End If
                        ' create connection to table
                        Call AttachDSNLessTable(.Name, sName, sServer, Cnct, sUser, sPWord)
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing
    
    MsgBox "Tables Re-Linked."

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & Err.Description
   Resume SLT_Exit

End Function
Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
    
AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music