Linked tables no longer linking after move from Access 97 -Access

[Ruth]

I use excel data tables that link into Access databases. Am only just moving
from Access 97 to Accss 2003 and there is a problem with links for any
spreadsheet tab that has a name which includes any space or other non
alphanumeric characters it seems. The links do not work any more after
converting the existing db into Access 2003 though the links worked in
Access97 . Looking in the linked table manager, the paths now read something
like
R:\foldernames\filename.xls\'name of tab'$ for the links that do not work
whereas path is something like
R:\foldernames\filename.xls\nospacesname$
for the spreadsheets where the link does still work.

There are LOTS of links like this in many spreadsheets, so I don't really
want to go through every spreadsheet manually, changing the tab names, and
then relinking in both the Access 97 and Access 2003 version of each database.
Any other way I can fix this please?

 

[Klatuu]

It has been a long time since I worked with 97, so I don't remember how it
worked then, but assuming R:\foldernames\filename.xls is the name of the file
and nospacesname$ is the name of the worksheet, then the worksheet name
should go in the Range argument of the TransferSpreadsheet method.

Also, names with spaces and special characters should be avoided. Use only
letters, numbers, and the underscore.

 

[Dale Fye]

In addition to Dave's comments, be advised that you will no longer be able
to "update" data in the linked Excel spreadsheets once you move to 2003,
although there are work-arounds.

If I remember correctly, you could still do that in 97 and 2000, but
settlement of a lawsuit between Microsoft and some developer somewhere
forced MS to disable that functionality in 2003
(http://www.theregister.co.uk/2006/02/07/microsoft_office_access_infringement/)

Dale

 

[Ruth]

Thanks but hope you can help further for how to get round this problem. I
want to avoid going back to every excel spreadsheet, renaming each linked
tab, relinking the excel tab into access 97 (because this is the version I'm
still using) then reconverting database to access 2003. Is there any way to
make the excel tabs that already link correctly in access97 also link
correctly in access 2003. The $ I've shown at the end of the example file
names is not part of the excel tab names but is something that access must
add as it shows up in the linked table manager listing. It seems to be that
the $ is being put in the wrong place when the conversion is attempted, which
is why the links no longer work - perhaps it should be 'name of tab$' rather
than 'name of tab'$ ? But is there a way I can edit the names of the tables
being linked to rather than going through the whole process with the wizard
of linking the tables - including selecting which fields to include, which
name to give the final linked table etc. That is what I want to avoid as it
will be a lot of work.

Perhaps what you say about "the worksheet name

should go in the Range argument of the TransferSpreadsheet method" should help . I'm afraid it means nothing to me, but if you can say step by step how I would make use of this, I could try.

Ruth