Relinking Excel table

[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

 

[George Nicholson]

Using the same connect string you do, I could re-link to a Sheet called
"Excel" in a specified Workbook (Access apparently adds the $ suffix when
the initial link is created so a SourceTableName value of 'Excel$' = Excel
sheet named 'Excel'. Pretty sure the $ signifies a sheet name while a string
w/o $ would specify a RangeName).

You do have a sheet named "Excel" in the targeted file, yes?

I was not able to recreate your specific error message even when i tried, so
I'm not sure where the problem is.

Therefore, the best I can suggest is: be sure all variables (especially
gXLFileLoc) have the expected values, and all specified files * and sheets*
exist with those names.

Also be sure your connect string reads "Excel 5.0" not Excel5.0". Pretty
sure you would get a different error if that were the case, but wordwrap
(see below) raises the issue of a missing space...

If you have done any linked table deletion & recreation recently, a compact
& repair might help also.

 

[Pete D.]

What version of Access?

 

[kagard]

Sorry... 2003, latest service pack.

Keith

 

[kagard]

Hi George:

Thanks for your reply. Yes, I do have a shee named "Excel" - the only
sheet in the workbook. I've double checked all the names and they
match up. I've also just compacted the db.

It looks like the columns in this sheet are different than the one the
user originally linked to. The workbook is produced from some web
based software retrieving data from an AS400 and displaying it in a
browser. The browser application has an export to Excel feature. The
data I'm looking at now isn't the same as the first time I linked to
it. Looks like the user deleted and renamed columns in the original
before I linked to it. Then, when they reran the report and replaced
the Excel file, it came in in its unmodified format. I created a test
db and excel file, linked the excel sheet, added and renamed columns
and reopened it in Access and it worked fine. Do you know if changing
the names and number of columns in the Excel workbook would impact
linking?

 

[George Nicholson]

Do you know if changing

the names and number of columns in the Excel workbook would impact
linking?

I honestly don't know. I haven't linked to Excel sheets enough to do
anything other than guess, although your experience certainly indicates that
would seem to be the case.

Changing column names would certainly impact Importing, but I'm not sure why
it would impact Linking, *unless* there was a pre-existing relationship that
would be orphaned by the re-linking process (iirc, you can create a
relationship with a Excel linked table, but can't enforce Referential
Integrity). I can easily see such a scenario throwing a "invalid Name"
error...