Refresh the links of tables to the SQL server backend through code

[reena]

Hi,

I am looking for a method that i can hard code a program to refresh the links to their appropriate DSN the backend being the SQL server. Instead of going to the users desktops each time when a change is made to the database and then refreshing the links through tools and then add-ins and linked table manager. All this is assuming that the userdesktops have their appropriate DSN created already.

Thanks a lot in advance.
Regards

 

[Pat Hartman]

Here's some code that might work for you. In my case, the data came from several different databases both Sybase and DB2. The code works fine for DB2 and I tested it for SQL Server for my own sanity but Sybase chokes so I was never able to use it. Since my tables came from so many data sources (there are only three tables in the sample posted below but the app had 30), I created a table that listed all the linked tables so that I could specify the various connect properties.

Option Compare Database
Option Explicit

Function CreateODBCLinkedTables() As Boolean
    On Error GoTo CreateODBCLinkedTables_Err
    Dim strTblName As String
    Dim strConn As String
    Dim strReg As String
    Dim db As Database
    Dim rs As Recordset
    Dim tbl As TableDef
         ' ---------------------------------------------
         ' Register ODBC database(s)
         ' ---------------------------------------------
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblODBCDataSources")
    With rs
        While Not .EOF
            If IsNull(rs!DSN) Then
            Else
                strReg = "Description= ERC7 - " & rs("DataBase")
                strReg = strReg & Chr(13) & "Server=" & rs("Server")
                strReg = strReg & Chr(13) & "Database=" & rs("DataBase")
                MsgBox strReg, vbInformation, "Registration string"
                DBEngine.RegisterDatabase rs("DSN"), rs("DriverName"), True, strReg
                
                ' ---------------------------------------------
                ' Link table
                ' ---------------------------------------------
                strTblName = rs("LocalTableName")
                strConn = "ODBC;"
                strConn = strConn & "DSN=" & rs("DSN") & ";"
                strConn = strConn & "SRVR=" & rs("Server") & ";"
                strConn = strConn & "APP=Microsoft Access;"
                strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
                strConn = strConn & "UID=" & rs("UID") & ";"
                strConn = strConn & "PWD=" & rs("PWD") & ";"
                strConn = strConn & "TABLE=" & rs("ODBCTableName")
                If (DoesTblExist(strTblName) = False) Then
                   Set tbl = db.CreateTableDef(strTblName, _
                                dbAttachSavePWD, rs("ODBCTableName"), _
                                strConn)
                      db.TableDefs.Append tbl
                    db.Containers.Refresh
                    MsgBox strConn, vbInformation, "Table link successfully created"
                Else
                    Set tbl = db.TableDefs(strTblName)
                    tbl.Connect = strConn
                    tbl.RefreshLink
                End If
            End If
                rs.MoveNext
        Wend
    End With
CreateODBCLinkedTables = True
db.Containers.Refresh
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
    Exit Function
CreateODBCLinkedTables_Err:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "MyApp"
    Resume CreateODBCLinkedTables_End
End Function

      '***************************************************************
      'The DoesTblExist function validates the existence of a TableDef
      'object in the current database. The result determines if an
      'object should be appended or its Connect property refreshed.
      '***************************************************************

Function DoesTblExist(strTblName As String) As Boolean
    On Error Resume Next
    Dim db As Database, tbl As TableDef
    Set db = CurrentDb
    Set tbl = db.TableDefs(strTblName)
    If Err.Number = 3265 Then   ' Item not found.
        DoesTblExist = False
        Exit Function
    End If
    DoesTblExist = True
End Function

Database	UID	PWD	Server	ODBCTableName	LocalTableName	DSN	DriverName
db2dgw_IP	ntanico	jules4	DB2DGW_IP	DB2D.CYTBCOMP	DB2D_CYTBCOMP	ERC_CIS2	Sybase 3.01 DirectCONNECT ODBC Driver
TMCNew	sa		(local)	dbo.LKUP_AUTH_TYP	dbo_LKUP_AUTH_TYP	ERC_SQL	SQL Server
e_cbo	cbo_dbo	keepsecret	USSTDD04	dbo.tb_user	dbo_tb_user	ERC_Security2	Sybase System 11