software

Windows XP, Office 2003

I use Access to build all my queries, and use 'get external data' to get the
data and build charts in Excel. As such, if I move the location of my Access
database, Excel gives me an error saying it cant find the file.

Does anyone know how I can move the Access database and then tell excel the
new location or something like that so it all still works?

Thanks,
Lou Sanderson

Edit the query - right click on the query and choose Edit=gt;Query.

--
Regards,
Tom Ogilvyquot;Lou Sandersonquot; wrote:

gt; Windows XP, Office 2003
gt;
gt; I use Access to build all my queries, and use 'get external data' to get the
gt; data and build charts in Excel. As such, if I move the location of my Access
gt; database, Excel gives me an error saying it cant find the file.
gt;
gt; Does anyone know how I can move the Access database and then tell excel the
gt; new location or something like that so it all still works?
gt;
gt; Thanks,
gt; Lou Sanderson

A quick way:
In VBA editor immediate pane, ?
Worksheets(quot;SheetNamequot;).QueryTables(1).Connection shows the connection
string; for Access normally this will include in it the path to the Access
file. Use the Replace function to replace this with the new file path, e.g:
Worksheets(quot;SheetNamequot;).QueryTables(1).Connection= Replace(Worksheets(quot;SheetNamequot;).QueryTables(1).Con nection ,quot;C:\old path\file.mdbquot;,quot;C:\new path\file.mdbquot;)

sorry about any word wrapping: the above should all be one long line
--
- K Dalesquot;Lou Sandersonquot; wrote:

gt; Windows XP, Office 2003
gt;
gt; I use Access to build all my queries, and use 'get external data' to get the
gt; data and build charts in Excel. As such, if I move the location of my Access
gt; database, Excel gives me an error saying it cant find the file.
gt;
gt; Does anyone know how I can move the Access database and then tell excel the
gt; new location or something like that so it all still works?
gt;
gt; Thanks,
gt; Lou Sanderson

You'll have to update Connection and CommandText attributes of the
querytable:

'------------
Sub ModQuery()
Const oldpath = quot;U:\Databases\mydatabasequot;
Const newpath = quot;C:\mydatabasequot;
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables(1)
With qt
.Connection = Replace(.Connection, oldpath, newpath)
.CommandText = Replace(.CommandText, oldpath, newpath)
End With
End Sub
'-----------

HTH
--
APquot;Lou Sandersonquot; gt; a écrit dans le
message de ...
gt; Windows XP, Office 2003
gt;
gt; I use Access to build all my queries, and use 'get external data' to get
the
gt; data and build charts in Excel. As such, if I move the location of my
Access
gt; database, Excel gives me an error saying it cant find the file.
gt;
gt; Does anyone know how I can move the Access database and then tell excel
the
gt; new location or something like that so it all still works?
gt;
gt; Thanks,
gt; Lou Sanderson
K Dales,

OK, this changed the location of the quot;DBQquot; (whatever that is) in the
connection string to the correct new location, but the quot;DefaultDirquot; remains
at the old location, and my error still exists....

Do I need to change the quot;DefaultDirquot; location as well, and if so can I have
help with that?

quot;K Dalesquot; wrote:

gt; A quick way:
gt; In VBA editor immediate pane, ?
gt; Worksheets(quot;SheetNamequot;).QueryTables(1).Connection shows the connection
gt; string; for Access normally this will include in it the path to the Access
gt; file. Use the Replace function to replace this with the new file path, e.g:
gt; Worksheets(quot;SheetNamequot;).QueryTables(1).Connection= Replace(Worksheets(quot;SheetNamequot;).QueryTables(1).Con nection ,quot;C:\old path\file.mdbquot;,quot;C:\new path\file.mdbquot;)
gt;
gt; sorry about any word wrapping: the above should all be one long line
gt; --
gt; - K Dales
gt;
gt;
gt; quot;Lou Sandersonquot; wrote:
gt;
gt; gt; Windows XP, Office 2003
gt; gt;
gt; gt; I use Access to build all my queries, and use 'get external data' to get the
gt; gt; data and build charts in Excel. As such, if I move the location of my Access
gt; gt; database, Excel gives me an error saying it cant find the file.
gt; gt;
gt; gt; Does anyone know how I can move the Access database and then tell excel the
gt; gt; new location or something like that so it all still works?
gt; gt;
gt; gt; Thanks,
gt; gt; Lou Sanderson