+ Reply to Thread
Results 1 to 8 of 8

Excel - MS Query - changing location of Access data source

  1. #1
    Thomas Becker
    Guest

    Excel - MS Query - changing location of Access data source

    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.



  2. #2
    William Benson
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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" <tom@beckertechnology.com> wrote in message
    news:%23apVqzYZFHA.2128@TK2MSFTNGP15.phx.gbl...
    > 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.
    >
    >




  3. #3
    Dick Kusleika
    Guest

    Re: Excel - MS Query - changing location of Access data source

    Thomas:

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


    --
    **** Kusleika
    Excel MVP
    Daily Dose of Excel
    www.*****-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.




  4. #4
    Thomas Becker
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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" <wbenson1@nycap.rr.com> wrote in message
    news:u8MbKlbZFHA.612@TK2MSFTNGP12.phx.gbl...
    > 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" <tom@beckertechnology.com> wrote in message
    > news:%23apVqzYZFHA.2128@TK2MSFTNGP15.phx.gbl...
    >> 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.
    >>
    >>

    >
    >




  5. #5
    Tom Ogilvy
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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" <tom@beckertechnology.com> wrote in message
    news:eNCcdieZFHA.2496@TK2MSFTNGP14.phx.gbl...
    > 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" <wbenson1@nycap.rr.com> wrote in message
    > news:u8MbKlbZFHA.612@TK2MSFTNGP12.phx.gbl...
    > > 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" <tom@beckertechnology.com> wrote in message
    > > news:%23apVqzYZFHA.2128@TK2MSFTNGP15.phx.gbl...
    > >> 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.
    > >>
    > >>

    > >
    > >

    >
    >




  6. #6
    Thomas Becker
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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.


    "**** Kusleika" <dkusleika@gmail.com> wrote in message
    news:e%238LnZeZFHA.2128@TK2MSFTNGP14.phx.gbl...
    > Thomas:
    >
    > http://www.*****-clicks.com/excel/Ex...htm#ChangeConn
    >
    >
    > --
    > **** Kusleika
    > Excel MVP
    > Daily Dose of Excel
    > www.*****-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.

    >
    >




  7. #7
    Thomas Becker
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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" <twogilvy@msn.com> wrote in message
    news:ud6g$GfZFHA.3852@TK2MSFTNGP10.phx.gbl...
    > 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" <tom@beckertechnology.com> wrote in message
    > news:eNCcdieZFHA.2496@TK2MSFTNGP14.phx.gbl...
    >> 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" <wbenson1@nycap.rr.com> wrote in message
    >> news:u8MbKlbZFHA.612@TK2MSFTNGP12.phx.gbl...
    >> > 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" <tom@beckertechnology.com> wrote in message
    >> > news:%23apVqzYZFHA.2128@TK2MSFTNGP15.phx.gbl...
    >> >> 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.
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  8. #8
    Dick Kusleika
    Guest

    Re: Excel - MS Query - changing location of Access data source

    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.


    --
    **** Kusleika
    Excel MVP
    Daily Dose of Excel
    www.*****-blog.com



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1