Thanks for the thought and, true, it would work. You can still get into the
query to edit the SQL even though the original data isn't there - your
method would eliminate the error message from coming up. You do have to
edit the SQL or you are left recreating the query anyway when you drop the
original table and insert the "new" (in location only) one.

The real problem is that the dialog box that pops up asking where the data
file is doesn't update the query with the new path and every other "work
around", while they work, is just that, "a work around".

I am hoping someone would have an easier way that doesn't involve code,
multiple steps, or other shenanigans to
make it work.

Thanks again for the suggestion.


"Tom Ogilvy" wrote in message
...
Why not have a copy of the data in both locations. then Edit the query
and
make the change manually. Then delete the copy in the old location.
Sounds like your only problem is you can't edit the query when the data
has
been moved - this should resolve that.

--
Regards,
Tom Ogilvy

"Thomas Becker" wrote in message
...
Thanks for the code - I have found similar suggestions so if I go this
direction I am pretty well set. I know I can code a solution - it just
seems dumb to have to "code" something when there should be a simple
manual
way to just change the
location of the mdb.

Thanks and have a great week.

"William Benson" wrote in message
...
The below VBA code deletes the old data and pastes back the data as a
result from a recordset ... but probably is not what you want.

I think if you record the steps to build the query manually in Excel,
using the Macro Recorder, and see what it is doing ... then set this
code
to a comand button including code to remove the old data, and intercept
the connection .commandlinetext property to refer to something built
off
a
stored value for a path, you should be ok.

Beware, .commandlinetext relies on an Array method of building a
string,
you will have to be careful with how you concatenate. It is not like
building a normal string using ampersand, I forget just how to do it,
but
you can substitute variables if you get the syntax right.




Sub RefreshData()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dbPath As String
Dim rsQuery As String
Dim w As Worksheet
Dim p As Worksheet
Dim pr As Range
Dim i As Long
Dim k As Long

Set w = activesheet
w.Range("A5:A65536").EntireRow.Delete

dbPath = '.... which you can define anywhere or
'insert code which makes the
user browse
'for it [requires more
code]

rsQuery = "SELECT * " & "FROM [MyTable] Where 1 = 1" 'insert other SQL
as
needed
Set db = OpenDatabase(dbPath, False, True)
Set rs = db.OpenRecordset(rsQuery, dbOpenSnapshot)
On Error Resume Next

If rs.RecordCount < 0 Then
With rs
k = 0
While Not .BOF And Not .EOF
.MoveFirst
w.Range("A2").Offset(k, 0).Value = .Fields(0).Value
'w.Range("A2").Offset(k, 1).Value = .Fields(1).Value etc,
etc...
k = k + 1
.MoveNext
Wend
End With
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub





"Thomas Becker" wrote in message
...
Using Office 2003.
Is there a way to use a macro to update query data source links when
the
path to the data source changes?

I created a spreadsheet and use MS Query to get data from a Access
database.
All works well.

I move the spreadsheet to another computer that uses a different path
to
the
Access database and the query fails because it can't find the
database -
the
dialog that pops up is entitled "MS Access driver LogIn Failed" and
the
error text is "Cannot find the file C:\test\testdata.mdb" Locating
the
access database in the login dialog that pops up next does not resolve
the
issue.

I can manually recreate the query but is seems like I should be able
to
store the path to the database in a cell and use a macro to recreate
everything.