link all tables but one using VBA


C

carrie schmid

I want to refresh link for all table but CASGND, how do I exclude it from
update

Function ASERVERLINK()
On Error GoTo Aserverlink_Err
Dim tdf As DAO.TableDef
Dim strD As String
Dim strC As String
Dim strFileName As String
Dim strIMEX As String
Dim db As DAO.Database
Set db = CurrentDb

strD = getPATH(db.Name) & "\"
For Each tdf In CurrentDb.TableDefs
If Len(Nz(tdf.Connect, "")) > 0 Then ' non-local
If Right(tdf.Connect, 3) = "xls" Then
If InStr(tdf.Connect, strD) = 0 Then ' need to
reconnect
strIMEX = Left(tdf.Connect, InStr(tdf.Connect,
"Database=") + 8)
strC = strIMEX & strD & tdf.Name & ".xls"
tdf.Connect = strC
tdf.RefreshLink
DoEvents
End If
Else
strFileName = getfile(Mid(tdf.Connect, 11))
If InStr(tdf.Connect, strD) = 0 Then ' need to
reconnect
tdf.Connect = (";DATABASE=" & strD & strFileName)
tdf.RefreshLink
End If
End If
End If
Next

Aserverlink_Exit:
Exit Function

Aserverlink_Err:
If Err.Number = 2450 Then 'form is missing
MsgBox "Cancelling Linking"
Resume Aserverlink_Exit
Else
MsgBox Err.description
Resume Aserverlink_Exit
End If
End Function
Function getPATH(FileName As String) As String
getPATH = DLookup("SERVER", "DATAINFO")
End Function
Function getfile(FileName As String) As String
getfile = Mid(FileName, InStrRev(FileName, "\") + 1)
End Function

thanks carrie
 
Ad

Advertisements

D

Douglas J. Steele

For Each tdf In CurrentDb.TableDefs
If Len(Nz(tdf.Connect, "")) > 0 Then ' non-local
If tdf.Name <> "CASGND" Then
If Right(tdf.Connect, 3) = "xls" Then
If InStr(tdf.Connect, strD) = 0 Then ' need to reconnect
strIMEX = Left(tdf.Connect, InStr(tdf.Connect, "Database=") + 8)
strC = strIMEX & strD & tdf.Name & ".xls"
tdf.Connect = strC
tdf.RefreshLink
DoEvents
End If
Else
strFileName = getfile(Mid(tdf.Connect, 11))
If InStr(tdf.Connect, strD) = 0 Then ' need to reconnect
tdf.Connect = (";DATABASE=" & strD & strFileName)
tdf.RefreshLink
End If
End If
End If
End If
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top