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.

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.

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.

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.

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.

Thomas:

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Thomas Becker wrote:
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.

Thank you for your link - I had given it a try but when I run it I hit the
refresh line and get a :Runtime error 1004: general odbc error"

Any thoughts? - While debugging I've checked all of the variables prior to
that line and they are set exactly as I would expect. I have Access2003
(with all updates) running on XPPro Sp2. Files are on local drive. I test
the code by simply moving the mdb to a new directory and re-opening the
spreadsheet.

Thanks again for the link - I will spend more time reviewing your
information.


"Dick Kusleika" wrote in message
...
Thomas:

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Thomas Becker wrote:
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.

Thomas Becker wrote:
Thank you for your link - I had given it a try but when I run it I
hit the refresh line and get a :Runtime error 1004: general odbc
error"
Any thoughts? - While debugging I've checked all of the variables
prior to that line and they are set exactly as I would expect. I
have Access2003 (with all updates) running on XPPro Sp2. Files are
on local drive. I test the code by simply moving the mdb to a new
directory and re-opening the spreadsheet.


Usually that means you have a syntax error in your connection string. Put a

Debug.Pring qt.Connection

line before the Refresh line and look at the string. It can be something
really small like a missing space or comma, so you really have to look
close. If you're sure you got it right, post the relevant portion of the
code and someone may be able to spot the problem.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com