How to remove a linked table and relink it through code
DawnTreader
I have a database that i am currently working on a new problem that i
have read about. specificall i was looking at the following article:
http://www.vb123.com.au/toolbox/07_access/multiuser.htm
on the page there is this:
"NOTE: If you make changes to fields or indexes on the server, you
must relink the remote tables in Access." This is due to the current
links caching structural and connection information for the remote
tables at the time of the link, and this information never gets
updated. To relink the tables, delete any existing linked tables.
Compact the database. Then re-establish the linked tables using File >
Get External Data > Link Tables..."
i do this a lot of adding fields and changes to the indexes in my
backend datafile. i had no idea i would need to do this relinking of
the tables in my frontend each time.
so what i would like to do is create a tool that will take a snapshot
of the list of currently linked tables and then remove them, compact
the frontend db and then add the tables again. this way i can make
sure that all my tables are "refreshed".
i have no idea where to start to get a list of my tables and what i
might need to store in a field to allow my code to find those tables
again after removing them and then connect them.
any one have any ideas?
DawnTreader
i was just thinking about it and realized that i have code already to
"refresh" the links of my tables. i use this code with 2 buttons on a
form. all this does is allow me to tell it to connect to my live
datafile or to my developement datafile. could i add some code to this
to remove the table and reconnect it?
'***************** Code Start ***************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fRefreshLinks(userStrPath As String) As Boolean
Dim strMsg As String
Dim collTbls As Collection
Dim i As Integer
Dim strDBPath As String
Dim strTbl As String
Dim dbCurr As Database
Dim dbLink As Database
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String
Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000
On Local Error GoTo fRefreshLinks_Err
If MsgBox("Are you sure you want to reconnect all Access tables?",
vbQuestion + vbYesNo, "Please confirm...") = vbNo Then
MsgBox "aborted"
Exit Function
End If
'First get all linked tables in a collection
Set collTbls = fGetLinkedTables
'now link all of them
Set dbCurr = CurrentDb
' strMsg = "Do you wish to specify a different path for the Access
Tables?"
'
' If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data
source...") = vbYes Then
' strNewPath = fGetMDBName("Please select a new datasource")
' Else
' strNewPath = vbNullString
' End If
strNewPath = userStrPath
For i = collTbls.Count To 1 Step -1
strDBPath = fParsePath(collTbls(i))
strTbl = fParseTable(collTbls(i))
varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl &
"'....")
If left$(strDBPath, 4) = "ODBC" Then
'ODBC Tables
'ODBC Tables handled separately
' Set tdfLocal = dbCurr.TableDefs(strTbl)
' With tdfLocal
' .Connect = pcCONNECT
' .RefreshLink
' collTbls.Remove (strTbl)
' End With
Else
If strNewPath <> vbNullString Then
'Try this first
strDBPath = strNewPath
Else
If Len(Dir(strDBPath)) = 0 Then
'File Doesn't Exist, call GetOpenFileName
strDBPath = userStrPath 'fGetMDBName("'" &
strDBPath & "' not found.")
If strDBPath = vbNullString Then
'user pressed cancel
err.Raise cERR_USERCANCEL
End If
End If
End If
'backend database exists
'putting it here since we could have
'tables from multiple sources
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
'check to see if the table is present in dbLink
strTbl = fParseTable(collTbls(i))
If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
err.Raise cERR_NOREMOTETABLE
End If
End If
Next
fRefreshLinks = True
varRet = SysCmd(acSysCmdClearStatus)
MsgBox "All Access tables were successfully reconnected.", _
vbInformation + vbOKOnly, _
"Success"
fRefreshLinks_End:
Set collTbls = Nothing
Set tdfLocal = Nothing
Set dbLink = Nothing
Set dbCurr = Nothing
Exit Function
fRefreshLinks_Err:
fRefreshLinks = False
Select Case err
Case 3059:
Case cERR_USERCANCEL:
MsgBox "No Database was specified, couldn't link tables.",
_
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case cERR_NOREMOTETABLE:
MsgBox "Table '" & strTbl & "' was not found in the
database" & _
vbCrLf & dbLink.Name & ". Couldn't refresh links",
_
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case Else:
strMsg = "Error Information..." & vbCrLf & vbCrLf
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
strMsg = strMsg & "Description: " & err.Description &
vbCrLf
strMsg = strMsg & "Error #: " & Format$(err.Number) &
vbCrLf
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
Resume fRefreshLinks_End
End Select
End Function
Function fIsRemoteTable(dbRemote As Database, strTbl As String) As
Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (err = 0)
Set tdf = Nothing
End Function
Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String
strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
fGetMDBName = "\\imwdb-01\servicedb\ServiceDB.mdb"
'ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function
Function fGetLinkedTables() As Collection
'Returns all linked tables
Dim collTables As New Collection
Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If left$(.Connect, 4) = "ODBC" Then
' collTables.Add Item:=.Name & ";" & .Connect,
KEY:=.Name
'ODBC Reconnect handled separately
Else
collTables.Add Item:=.Name & .Connect, Key:=.Name
End If
End If
End With
Next
Set fGetLinkedTables = collTables
Set collTables = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
Function fParsePath(strIn As String) As String
If left$(strIn, 4) <> "ODBC" Then
fParsePath = right(strIn, Len(strIn) - (InStr(1, strIn,
"DATABASE=") + 8))
Else
fParsePath = strIn
End If
End Function
Function fParseTable(strIn As String) As String
fParseTable = left$(strIn, InStr(1, strIn, ";") - 1)
End Function
'***************** Code End ***************
if so where and what? i was thinking that if added code here:
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
'check to see if the table is present in dbLink
strTbl = fParseTable(collTbls(i))
If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
err.Raise cERR_NOREMOTETABLE
End If
before the action of reconnecting the table that i could delete the
table and relink it somewhere here. but the question is how if the
table is removed will it know what table to relink?.
Tony Toews
<alan...@gmail.com> wrote:
>"NOTE: If you make changes to fields or indexes on the server, you
>must relink the remote tables in Access." This is due to the current
>links caching structural and connection information for the remote
>tables at the time of the link, and this information never gets
>updated. To relink the tables, delete any existing linked tables.
>Compact the database. Then re-establish the linked tables using File >
>Get External Data > Link Tables..."
>
>i do this a lot of adding fields and changes to the indexes in my
>backend datafile. i had no idea i would need to do this relinking of
>the tables in my frontend each time.
Well, that's not as big a deal as it once was. You would
occasionally get the dreaded -1517 error but I haven't seen that one
for a long time so I suspect the Access team has fixed that problem.
http://www.granite.ab.ca/access/reservederror1517.htm
Basically put if you haven't seen any issues then I wouldn't worry too
much about it.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Tony Toews
>if so where and what? i was thinking that if added code here:
>
> Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
>
> 'check to see if the table is present in dbLink
> strTbl = fParseTable(collTbls(i))
> If fIsRemoteTable(dbLink, strTbl) Then
> 'everything's ok, reconnect
> Set tdfLocal = dbCurr.TableDefs(strTbl)
> With tdfLocal
> .Connect = ";Database=" & strDBPath
> .RefreshLink
> collTbls.Remove (.Name)
> End With
> Else
> err.Raise cERR_NOREMOTETABLE
> End If
>
>before the action of reconnecting the table that i could delete the
>table and relink it somewhere here. but the question is how if the
>table is removed will it know what table to relink?.
Sample code follows.
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.Name & ", " & tdf.Connect
Next
Set tdf = CurrentDb.CreateTableDef("test")
tdf.Connect = ";DATABASE=Q:\1 access\testing\a2002.mdb"
tdf.SourceTableName = "Table1"
CurrentDb.TableDefs.Append tdf
Now the only problem is this doesn't refresh the list of tables in the
database window. Currentdb.tabledefs.refresh doesn't do that. And
I've forgotten what it is right now. F5 at the database windows will
do that but that's not sufficient.
DawnTreader
> On Fri, 2 Sep 2011 10:35:58 -0700 (PDT), DawnTreader
>
> >"NOTE: If you make changes to fields or indexes on the server, you
> >must relink the remote tables in Access." This is due to the current
> >links caching structural and connection information for the remote
> >tables at the time of the link, and this information never gets
> >updated. To relink the tables, delete any existing linked tables.
> >Compact the database. Then re-establish the linked tables using File >
> >Get External Data > Link Tables..."
>
> >i do this a lot of adding fields and changes to the indexes in my
> >backend datafile. i had no idea i would need to do this relinking of
> >the tables in my frontend each time.
>
> Well, that's not as big a deal as it once was. You would
> occasionally get the dreaded -1517 error but I haven't seen that one
>
> Basically put if you haven't seen any issues then I wouldn't worry too
> much about it.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
> For a convenient utility to keep your users FEs and other files
> updated seehttp://www.autofeupdater.com/
But could it contribute to slowness in the application frontends? it
is not about issues, it is about making my app more efficient and
speedy. it is probably the biggest issue we have with our app.
Tony Toews
<alan...@gmail.com> wrote:
>But could it contribute to slowness in the application frontends? it
>is not about issues, it is about making my app more efficient and
>speedy. it is probably the biggest issue we have with our app.
Possible but unlikely. I would suspect that any cached data is
refreshed as appropriately in the Access FE the user sees but only
once. After that it's fine. Although I sure could be wrong.
What kind of performance issues are you having? Have you visited my
Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/