If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer.
I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time programmer and eventually this little system has to be used by actual users. So after thorough testing, I move these two little spreadsheets over to a users machine... Anyway all works fine until I move fred and bob to a new home. If I move fred and bob to different directory on the same computer or to say a users computer where I have to change the directory name, the query stops working. ODBC returns a error messages that "c:\123directory\bob.xls doesn't exist". So, I edited the macro and changed the path names (in all 3 places) to reflect the new directory path where fred and bob live. I still get the ODBC error message. Why is the query still looking for c:\123direcotry\bob.xls when I clearly told the macro that bob now lives at c:\abcdirecotry\bob.xls ????? I then edited the query fetch_from_bob by going to the external data area and activating a cell, then doing Data/Edit query. I then changed the path name in the from portion of the sql staement. The query editor will not let me close/ save the file. It gives an error message like "can't use parameters in a query that can't be displayed graphically" ??Huh ?? what is that ? I'm postive that the sql syntax i correct, as I onle changed the path portion from "c:\123director\bob.xls" to "c:\abcdirectory\bob.xls" With this bizarre behavior, I decided to different route. I edit the file fetch_from_bob.dqy with notepad. I make real sure to the edit the query that lives in the same new directory "c:\abcdirectory" with bob and fred. Again I change the sql from to reflect the new desired path and close/save the file. When I go back into the spreadsheet and execute the query/refresh data, I get the same old ODBC error "c:\123directory\bob.xls doesn't exist". Why is query still looking for "c:\123directory\bob.xls" when I've clearly just told it that bob now lives at c:\abcdirectory\bob.xls" ??? Anyway, I kept fooling around until I noticed that when you do File..Open while in the query editor that query editor is looking down a path called User(whoever you happen to be logged on as)\Application Data\Microsoft\Queries\fetch_from_bob.dqy If you open this instance of fetch_from_bob.dqy, you find that query still has an sql from stagement that is looking down the old path "c:\123directory\bob.xls" . How did this file get there ??? Is query building hidden files in the background ?? So, I go over to explorer and search high and low for USER\Application Data\Microsoft\Queries\*.*. Explorer has never heard of any such file or path. Explorer has a USER with many subdirectories, but not one called Appication Data. I've tried this on 3 computers logged in as mutiple different users. I get the same bogus result on all machines. Clearly, Query is building some hidden program files in the background then using those files instead of the files I'm expecting it to use. How do I find\change those hidden files ? PS... All machines have some XP varient and I am logged on as a "user" on each machine not and "administrator" |
#2
|
|||
|
|||
Hidden files in Ms-Query cause ODBC connect errors or Query is wac
On Fri, 9 Jul 2004 16:49:02 -0700, needyourhelp
wrote: The 'hidden files!!' panic may be a little over the top - I think that you need to change your path in the DSN (Data Source Name). Go to control panel and change the path to the file in the ODBC 32-bit data sources. Select the data source you created when you first created the spreadsheet. On another machine, you may have to create the data source. PS Don't look now, but there's a black helicopter hovering outside your window!! ;-) Mike Argy Custom Office Solutions and Windows/UNIX applications Please post on-topic responses to the newsgroup To e-mail me, remove nospam from the address in the headers |
#3
|
|||
|
|||
Hidden files in Ms-Query cause ODBC connect errors or Query is wac
Mike A wrote ...
I think that you need to change your path in the DSN (Data Source Name). DSN? The OP didn't mention one. Rather, it may be the connection string that requires amending. See: http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn Jamie. -- |
#5
|
|||
|
|||
Hidden files in Ms-Query cause ODBC connect errors or Query is
Mike,
Thanks for your help. No panic and no black helicopters here. Just silly software behavior.... I am on a different machine from the original development box, so... I tried recreating the ODBC data source several times. No go. Same error. Question ? Should I be using USER DSN, SYSTEM DSN or FILE DSN. I tried changing all 3. Question ? What should the name of the ODBC data source be... The name of the query as saved by ms-query ? The name of the External Data Range Properties ? I've noticed excel tends to change this from whatever name you've given it to "Query from Excel Files" on somehwat of a random basis. Can't figure out what triggers this change. The name of the actual target excel file that I would like query/odbc to actually extract data from? I've tried all three names in all 3 DSN places on create new ODBC data source. The only thing that works is to just erase the named external data range and recreate the query. That works every time. It also tells tme that query/odbc is creating something somewhere that I can't find change. I could to recreate query on each user machine, but I don't want to unless I have to, as I'd like a single source code resource. Any more advice as I'm beyond stumped at this point. I just don't get why query/odbc and vb-macro/odbc is simply ignoring explicit instructions of which dsn string to use for the connection. Also, you didn't address the issue of ...\Application Data\Mircosoft\Queries at all. Do you know why query is looking down this otherwise hidden or no-exitant path ? thanks, tim "Mike A" wrote: On Fri, 9 Jul 2004 16:49:02 -0700, needyourhelp wrote: The 'hidden files!!' panic may be a little over the top - I think that you need to change your path in the DSN (Data Source Name). Go to control panel and change the path to the file in the ODBC 32-bit data sources. Select the data source you created when you first created the spreadsheet. On another machine, you may have to create the data source. PS Don't look now, but there's a black helicopter hovering outside your window!! ;-) Mike Argy Custom Office Solutions and Windows/UNIX applications Please post on-topic responses to the newsgroup To e-mail me, remove nospam from the address in the headers |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
problems initiating mplay32.exe | electricrainbowfish | Powerpoint | 8 | July 8th, 2004 09:37 AM |
Struggling with MS Query... | Alex | General Discussion | 5 | July 6th, 2004 11:46 AM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Office XP | Ed Lester | Setup, Installing & Configuration | 1 | May 27th, 2004 09:30 AM |