Thanks Patrick,
I appreciate the code so that I don't have to re-invent the wheel.
Glenn
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patrick Wood
Sent: August-24-12 11:44 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Changing links to multiple back ends
Hi Glenn,
John's comment helped me to figure out what I was doing wrong so I was able
to write a procedure that relinks Excel files. So just in case you need
here is the procedure.
Public Function RefreshExcelLink() As String
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer
Dim intPlace As Integer
Set db = CurrentDb
'Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
'Verify the table is a linked table.
If Left(tdf.Connect, 5) = "Excel" Then
'Get the existing Connection String.
strCon = Nz(tdf.Connect, "")
'Get the name of the back-end Excel File using String Functions.
strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon,
"\") - 1)))
'Verify we have a value for the back-end
If Len(strBackEnd & "") > 0 Then
'Set a reference to the TableDef Object.
Set tdf = db.TableDefs(tdf.Name)
'Build the new Connection Property Value.
'Get the location of where ";DATABASE=" starts in the
connection
'string so we can add the CurrentProject Path after that.
intPlace = InStr(strCon, ";DATABASE=")
'Build the Connect string using the first part of the
existing connection string
tdf.Connect = Left(strCon, intPlace) & "DATABASE=" &
CurrentProject.Path & strBackEnd
'Refresh the table link.
tdf.RefreshLink
Else
'There was a problem getting the name of the back-end.
'Add the information to the message to notify the user.
intErrorCount = intErrorCount + 1
strMsg = strMsg & "Error getting back-end database name." &
vbNewLine
strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine
strMsg = strMsg & "Connect = " & strCon & vbNewLine
End If
End If
Next tdf
ExitHere:
On Error Resume Next
If intErrorCount > 0 Then
strMsg = "There were errors refreshing the table links: " _
& vbNewLine & strMsg & "In Procedure RefreshExcelLink"
RefreshExcelLink = strMsg
End If
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
intErrorCount = intErrorCount + 1
strMsg = strMsg & "Error " & Err.Number & " " & Err.Description
strMsg = strMsg & vbNewLine & "Table Name: " & tdf.Name & vbNewLine
strMsg = strMsg & "Connect = " & strCon & vbNewLine
Resume ExitHere
End Function
Kind Regards,
Patrick Wood
Microsoft Access MVP
Gaining Access Technologies Founder
http://gainingaccess.net
Microsoft Access and SQL Azure Information Center for Developers:
http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Friday, August 24, 2012 7:12 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends
Thanks Patrick,
John has given me a lead so I will try to get that to work.
Glenn
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Patrick
Wood
Sent: August-24-12 6:04 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends
Hi Glen,
You cannot do it with the RefreshLink method. I am not sure you can use code
to delete the existing TableDef and add a new one but it is very late and I
do not have time to test it now.
Kind Regards,
Pat
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar