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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ruth - 25 Mar 2008 12:24 GMT

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?

Reply to this Message

Klatuu - 25 Mar 2008 18:48 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 12 lines]
> then relinking in both the Access 97 and Access 2003 version of each database.
> Any other way I can fix this please?

Reply to this Message

Ruth - 28 Mar 2008 10:36 GMT

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

> 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
[quoted text clipped - 20 lines]
> > then relinking in both the Access 97 and Access 2003 version of each database.
> > Any other way I can fix this please?

Reply to this Message

Dale Fye - 26 Mar 2008 01:28 GMT

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

>I use excel data tables that link into Access databases. Am only just
>moving
[quoted text clipped - 15 lines]
> database.
> Any other way I can fix this please?

Reply to this Message