change link path for linked spreadsheet

[franc sutherland]

Hello everyone,

I am using Access 2003. In my database there is a table which is
linked to an excel spreadsheet. When I move the mdb file to someone
else's computer the spreadsheet will be in a different location. A
location which I can not access from my own PC when designing the
database, so can not use the Linked Table Manager to update the link.
I need to find a way of telling the database where the source
spreadsheet for the link table is.

Is there a way of using a public constant which would store a string
which would be the network path to the spreadsheet, which I could
combine with a startup module which would tell the database where to
find the source spreadsheet?

I have tried using the fRefreshLinks function from the Microsoft
website but this would appear to be designed for re-locating BE mdb's
rather than spreadsheets and gives me error number 3343, unrecognised
database format, followed by the existing file and path name for the
spreadsheet.

Thanks,

Franc.

[KC-Mass]

Hi Franc

Store the path in a control on a form and reference that
when relinking. That way you can edit it and relink where
you need to.

Google for code to "Select a directory VBA" and
"relink Access to excel file VBA".

You should get everything you need.
Regards Kevin
"franc sutherland" <fr**************@googlemail.comwrote in message
news:96**********************************@p59g2000 hsd.googlegroups.com...

Hello everyone,

I am using Access 2003. In my database there is a table which is
linked to an excel spreadsheet. When I move the mdb file to someone
else's computer the spreadsheet will be in a different location. A
location which I can not access from my own PC when designing the
database, so can not use the Linked Table Manager to update the link.
I need to find a way of telling the database where the source
spreadsheet for the link table is.

Is there a way of using a public constant which would store a string
which would be the network path to the spreadsheet, which I could
combine with a startup module which would tell the database where to
find the source spreadsheet?

I have tried using the fRefreshLinks function from the Microsoft
website but this would appear to be designed for re-locating BE mdb's
rather than spreadsheets and gives me error number 3343, unrecognised
database format, followed by the existing file and path name for the
spreadsheet.

Thanks,

Franc.

[franc sutherland]

On Oct 22, 5:17*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

Hi Franc

Store the path in a control on a form and reference that
when relinking. *That way you can edit it and relink where
you need to.

Google for code to "Select a directory VBA" and
"relink Access to excel file VBA".

You should get everything you need.

Regards Kevin

"franc sutherland" <[e-mail] in message

news:96**********************************@p59g2000 hsd.googlegroups.com...

Hello everyone,

I am using Access 2003. *In my database there is a table which is
linked to an excel spreadsheet. *When I move the mdb file to someone
else's computer the spreadsheet will be in a different location. *A
location which I can not access from my own PC when designing the
database, so can not use the Linked Table Manager to update the link.
I need to find a way of telling the database where the source
spreadsheet for the link table is.

Is there a way of using a public constant which would store a string
which would be the network path to the spreadsheet, which I could
combine with a startup module which would tell the database where to
find the source spreadsheet?

I have tried using the fRefreshLinks function from the Microsoft
website but this would appear to be designed for re-locating BE mdb's
rather than spreadsheets and gives me error number 3343, unrecognised
database format, followed by the existing file and path name for the
spreadsheet.

Thanks,

Franc.

Hi Kevin,

Thanks for the advice. I found some useful stuff here
http://p2p.wrox.com/topicindex/31037.htm in case anyone else is on
this as well.

All the best,

Franc.