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
Hi. There are two approaches to relinking a table. One way is to delete the table and relink it, as Vlad's code does. The other way is to modify the linked tables' Connect property and then refresh the link.could you possibly explain to me how to do this please?
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
I have 3 back ends on different function for different departments but in the end they all tie in"Numerous back end files" sounds ominous. How many is "numerous" in this context?