+ Reply to Thread
Results 1 to 3 of 3

Excel query of Access database - changed database filename, now error

  1. #1

    Excel query of Access database - changed database filename, now error

    I have an excel workbook where I've queried a table in access. When I
    first setup the query, I did it through the get external data query
    wizard, browsed, found my access database (called database_010306.mdb)
    and selected my table. All was fine.

    Now, I am renaming my temporary named database (database_010306.mdb) to
    just database.mdb. So I've got to tell Excel the new filename.

    I thought I had it when I went to "edit query" in Excel, then went to
    "Microsoft Query", looked at the SQL code and changed the FROM
    statement to point to my new filename. When I closed Microsoft Query,
    it successfully updated the query with the correct data from the newly
    renamed database.mdb.

    but when I use the "refresh query" buttons on the external data
    toolbar, it tells me it can't find database_010306.mdb - ODBC access
    login error or something. yet, when I again look at the query in
    microsoft query, it has the correct (new) filename in the FROM
    statement, and again when I close Microsoft query, it refreshes as it
    should have when I click on "refresh data".

    Where else is this old filename stored, and can I simply edit it
    without having to make a new query with the query wizard and point to
    the new mdb file?

    I hesitate to make all new queries since I have many sheets with many
    queries and would like to see if there is a place I can just go and
    change the filename, as updating in microsoft query did not seem to do
    it.

    Thanks


  2. #2
    Ardus Petus
    Guest

    Re: Excel query of Access database - changed database filename, now error

    You have to update the database name in
    ActiveSheet.QueryTables(1).Connection

    HTH
    --
    AP

    <[email protected]> a écrit dans le message de
    news:[email protected]...
    > I have an excel workbook where I've queried a table in access. When I
    > first setup the query, I did it through the get external data query
    > wizard, browsed, found my access database (called database_010306.mdb)
    > and selected my table. All was fine.
    >
    > Now, I am renaming my temporary named database (database_010306.mdb) to
    > just database.mdb. So I've got to tell Excel the new filename.
    >
    > I thought I had it when I went to "edit query" in Excel, then went to
    > "Microsoft Query", looked at the SQL code and changed the FROM
    > statement to point to my new filename. When I closed Microsoft Query,
    > it successfully updated the query with the correct data from the newly
    > renamed database.mdb.
    >
    > but when I use the "refresh query" buttons on the external data
    > toolbar, it tells me it can't find database_010306.mdb - ODBC access
    > login error or something. yet, when I again look at the query in
    > microsoft query, it has the correct (new) filename in the FROM
    > statement, and again when I close Microsoft query, it refreshes as it
    > should have when I click on "refresh data".
    >
    > Where else is this old filename stored, and can I simply edit it
    > without having to make a new query with the query wizard and point to
    > the new mdb file?
    >
    > I hesitate to make all new queries since I have many sheets with many
    > queries and would like to see if there is a place I can just go and
    > change the filename, as updating in microsoft query did not seem to do
    > it.
    >
    > Thanks
    >




  3. #3

    Re: Excel query of Access database - changed database filename, now error

    Thanks for the reply Ardus. How do I get to that? I looked in the VBA
    explorer, couldn't find it there.


+ 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