[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
Public Function RefreshTableLinks(strEnvironment As String, strAddRefresh As String, _
Optional strTableName As String) As String
'strEnvironment should be either 'production' or 'development'
'refreshes existing ODBC table links or adds new ODBC table link depending on value of strAddRefresh
'use strAddRefresh = refresh or strAddRefresh = add
'new ODBC linked table is added as name = strTableName with _ and first four characters of strEnvrionment appended
'returns "fail" on fail, returns name linked table (add) or name of last linked table (refresh) on success
On Error GoTo Err_RefreshTableLinks
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim strLink As String, strDBName As String, strLocalTableName As String
Dim strMsg As String, strProdLink As String, strDevLink As String
Dim strSuffix As String, strResult As String
Dim strLinkedTableName As String
'initialize return variable
strResult = "fail"
'make sure we were passed expected values
If strEnvironment <> "production" And strEnvironment <> "development" Then GoTo Exit_RefreshTableLinks
If strAddRefresh <> "refresh" And strAddRefresh <> "add" Then GoTo Exit_RefreshTableLinks
'name of local table with connection string info
strLocalTableName = "SQL_ConnectionStrings"
'name of SQL database we want to reconnect to
strDBName = "NameOfSQLServerDb"
'open recordset (as dynaset so we can use Find methods) on table and find record
'for named database
Set rs = CurrentDb.OpenRecordset(strLocalTableName, dbOpenDynaset)
rs.FindFirst "SQLdbName = " & Chr(34) & strDBName & Chr(34)
If rs.NoMatch Then
strMsg = "No record for the database " & strDBName & " could be found in table " _
& strTableName & "."
MsgBox strMsg, , "Error Re-Linking SQL Tables"
'retrieve both production and development connection strings from table
strProdLink = rs!PRD_ConnectString
strDevLink = rs!DEV_ConnectString
Select Case strEnvironment
strLink = strProdLink
strLink = strDevLink
Case Else 'do nothing
'proceed based upon passed value of strAddRefresh
Select Case strAddRefresh
'iterate through table definitions and refresh links for tables that have connection
For Each tdf In CurrentDb.TableDefs
'get table name as return value
strResult = tdf.Name
If Len(tdf.Connect) > 0 Then
If InStr(tdf.Connect, "SQL Server") Then
tdf.Connect = strLink
'make sure we received a non-empty value for the option table name variable
If strTableName = "" Then
strMsg = "You must pass a valid table name for the table you want to link to."
MsgBox strMsg, , "Function RefreshTableLinks"
'create suffix string we'll append to passed table name
strSuffix = "_" & Left(strEnvironment, 4)
'create name for linked table
strLinkedTableName = "dbo_" & strTableName & strSuffix
'link table using the specified connection string
DoCmd.TransferDatabase acLink, "ODBC Database", strLink, acTable, strTableName, _
'set return value to table name
strResult = strLinkedTableName
'clear object variables
Set tdf = Nothing
Set rs = Nothing
'function return value
RefreshTableLinks = strResult
MsgBox Err.Number & ", " & Err.Description, , "Error in function RefreshTableLinks"
Open in new window
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.