Hi. If you are talking using about a global variable, then you are also talking about using code to relink your tables. If so, then the answer is a yes. In fact, you can also use a table to store your UNC path and use it in your code.another question on the UNC path
I have quite a few different databases, most interlink
all the tables must be linked into the front ends one by one
is there a way that I could have a global UNC path?
this way, if the IT manager changes servers, I can just change the global setting and not have to change every single linked table in all the different databases?
could you possibly explain to me how to do this please?Hi. If you are talking using about a global variable, then you are also talking about using code to relink your tables. If so, then the answer is a yes. In fact, you can also use a table to store your UNC path and use it in your code.
Dim sLocalTable As String, sSourceTable As String, sDatabaseType As String, sConnect As String, sType As String Dim tdfNew As DAO.TableDef, rstTables As DAO.Recordset Set rstTables = appAccess.CurrentDb.OpenRecordset("usysFUZE_Tables", dbOpenDynaset) 'Lets Loop through hlpTables Do Until rstTables.EOF sLocalTable = rstTables("LocalTable") sSourceTable = rstTables("SourceTable") sConnect = rstTables("ConnectionString") sType = rstTables("TableType") If sType <> "Local" Then 'we have to update the connect string appAccess.DoCmd.DeleteObject acTable, sLocalTable If sType = "Access" Then appAccess.DoCmd.TransferDatabase acLink, "Microsoft Access", Replace(sConnect, ";DATABASE=", ""), acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable ElseIf sType = "ODBC" Then appAccess.DoCmd.TransferDatabase acLink, "ODBC Database", sConnect, acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable, False, True End If End If rstTables.MoveNext Loop
Public Function vcLinkTableDefs() Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim strNewConnectionString As String On Error Resume Next Set dbs = CurrentDb() ' Loop through TableDefs collection, only processing ' the table if it already has a Connection property. ' (all other tables are local ... not linked) For Each tdf In dbs.TableDefs If tdf.Connect <> "" Then strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]") If tdf.Connect <> strNewConnectionString Then tdf.Connect = strNewConnectionString tdf.RefreshLink End If End If Next End Function