A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hidden files in Ms-Query cause ODBC connect errors or Query is wac



 
 
Thread Tools Display Modes
  #1  
Old July 10th, 2004, 12:49 AM
needyourhelp
external usenet poster
 
Posts: n/a
Default 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  
Old July 10th, 2004, 01:17 AM
Mike A
external usenet poster
 
Posts: n/a
Default 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  
Old July 10th, 2004, 10:32 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default 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.

--
  #4  
Old July 10th, 2004, 12:39 PM
Mike A
external usenet poster
 
Posts: n/a
Default Hidden files in Ms-Query cause ODBC connect errors or Query is wac

On 10 Jul 2004 02:32:31 -0700, (Jamie Collins)
wrote:

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.


It was a shot in the dark - the OP may have created a new DSN the
first time MS Query was used, not realizing that it is not part of the
query (it is a setting for the local OS) and will not be
copied/updated with the query or the workbook.

From the link you posted:

....the Connection string may contain a DefaultDir element that points
to a path. You don't necessarily have to change the DefaultDir, but
if the directory that exists there doesn't exist on your hard drive,
you will get an error...

Another possibility!


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
  #5  
Old July 12th, 2004, 09:38 PM
needyourhelp
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.