K
kagard
Greetings:
I have written a sub that reads the back end data location from a text
file in my application folder. It successfully relinks the access
tables, but I have an Excel table that won't reconnect. It gives me an
Error 3125 indicating that " (not a typo, just a single double quote)
isn't a valid name.
The path and filename are correct and the Excel file actual exists in
the specified location. Execution fails on the Refresh Link statement,
but is, no doubt, cause by the Connect on the preceding line. Here's
the code:
Public Sub RelinkAllTables()
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer
Dim StartupFileLocation As String
Dim LocationsFile As Object
Dim TextLine As String
On Error GoTo FixRelinking
'Set path and file name for startup file
StartupFileLocation = Application.CurrentProject.path &
"\FileLocations.txt"
'Read paths from location file
Open StartupFileLocation For Input As #1
Line Input #1, gDBFileLoc
Line Input #1, gXLFileLoc
Close #1
Set db = DBEngine.Workspaces(0).Databases(0)
For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).Connect) > 1 Then
If db.TableDefs(i).SourceTableName = "Excel$" Then
'Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\DCdb
\BrandSalesYTD.xls
db.TableDefs(i).Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=" & gXLFileLoc
db.TableDefs(i).RefreshLink
Else
db.TableDefs(i).Connect = ";Database=" & gDBFileLoc
db.TableDefs(i).RefreshLink
End If
End If
Next
Exit Sub
FixRelinking:
Dim strMessage As String
strMessage = "There has been a problem relinking the data files."
_
& vbCrLf & Err.Description _
& vbCrLf & "Data: " & gDBFileLoc _
& vbCrLf & "XL: " & gXLFileLoc
MsgBox strMessage, vbOKOnly, "Relinking error (" & Err.Number &
")"
End Sub
Does anyone have an idea of why this isn't working? TIA.
Keith
I have written a sub that reads the back end data location from a text
file in my application folder. It successfully relinks the access
tables, but I have an Excel table that won't reconnect. It gives me an
Error 3125 indicating that " (not a typo, just a single double quote)
isn't a valid name.
The path and filename are correct and the Excel file actual exists in
the specified location. Execution fails on the Refresh Link statement,
but is, no doubt, cause by the Connect on the preceding line. Here's
the code:
Public Sub RelinkAllTables()
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer
Dim StartupFileLocation As String
Dim LocationsFile As Object
Dim TextLine As String
On Error GoTo FixRelinking
'Set path and file name for startup file
StartupFileLocation = Application.CurrentProject.path &
"\FileLocations.txt"
'Read paths from location file
Open StartupFileLocation For Input As #1
Line Input #1, gDBFileLoc
Line Input #1, gXLFileLoc
Close #1
Set db = DBEngine.Workspaces(0).Databases(0)
For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).Connect) > 1 Then
If db.TableDefs(i).SourceTableName = "Excel$" Then
'Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\DCdb
\BrandSalesYTD.xls
db.TableDefs(i).Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=" & gXLFileLoc
db.TableDefs(i).RefreshLink
Else
db.TableDefs(i).Connect = ";Database=" & gDBFileLoc
db.TableDefs(i).RefreshLink
End If
End If
Next
Exit Sub
FixRelinking:
Dim strMessage As String
strMessage = "There has been a problem relinking the data files."
_
& vbCrLf & Err.Description _
& vbCrLf & "Data: " & gDBFileLoc _
& vbCrLf & "XL: " & gXLFileLoc
MsgBox strMessage, vbOKOnly, "Relinking error (" & Err.Number &
")"
End Sub
Does anyone have an idea of why this isn't working? TIA.
Keith