By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

change link path for linked spreadsheet

P: n/a
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.

Oct 21 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Oct 22 '08 #2

P: n/a
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" <franc.sutherl...@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.
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.
Oct 22 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.