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"
0
7/9/2004 11:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
742 Views

Similar Articles

[PageSpeed] 31

On Fri, 9 Jul 2004 16:49:02 -0700, needyourhelp
<needyourhelp@discussions.microsoft.com> 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
0
mikeargy2 (30)
7/10/2004 12:17:52 AM
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/ExternalData5.htm#ChangeConn

Jamie.

--
0
jamiecollins (192)
7/10/2004 9:32:31 AM
On 10 Jul 2004 02:32:31 -0700, jamiecollins@xsmail.com (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/ExternalData5.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
0
mikeargy2 (30)
7/10/2004 11:39:58 AM
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
> <needyourhelp@discussions.microsoft.com> 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
> 
0
7/12/2004 8:38:02 PM
Reply:

Similar Artilces:

Error after sync
Can anyone suggest how to fic=x this error: Detailed technical information follows: --- Date and Time: 8/26/2007 10:14:46 PM Machine Name: DELL IP Address: 192.168.2.69 Current User: DELL\Richard Filiberto Application Domain: C:\TMW8\ Assembly Codebase: file:///C:/TMW8/AddinExpress.OL.2005.DLL Assembly Full Name: AddinExpress.OL.2005, Version=3.4.908.2005, Culture=neutral, PublicKeyToken=e69a7e0f3592d2b8 Assembly Version: 3.4.908.2005 Assembly Build Date: 6/27/2002 2:06:50 AM Exception Source: AddinExpress.OL.2005 Exception Type: ...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

Unable to read file #8
Office XP - excel 2002 - All of s sudden I cannot read any of my excel files. Does anyone have a fix for this? Hi Ron! If you can open Excel and open files from within Excel, You may want to try one of these: Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Run excel /regserver The /unregserver & /regserver stuff resets the windows registry to excel's factory defaults. An alternative might be to do: Try Tools|Options|General|Ignore other applications (uncheck it) Failing that, what error messages are you getting? -- Regards Norman Harker ...

Quirky recent file list #2
Debra--- I do tend to open from Windows Explore more then File>Open. But I am saving the file. It's more that it is annoying when I close a file by accident and don't quite remember where it got saved on the server. Stacie -- SPenney ------------------------------------------------------------------------ SPenney's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=267592 Well, I guess you weren't the only one who wanted this behaviour changed. <g> If/when you upgr...

remote desktop web connection
Does anyone have info on whether I can use remote desktop web connection on a lan as an emulator to connect to another app? Can I use remote desktop web connection to access terminal services? ...

Linking a graph to a csv file
**Excel 2002 Hello, I'm sure this is exceedingly easy, but somehow, I'm screwing this up. I ultimately need to have a self- updating PowerPoint file (client mandates a ppt file). According to ppt's help, that means I have to use linked Excel graphs, not embedded ppt graphs. However, my programmer will be supplying me with tons of csv files. When I try to create an excel file with a data link to a csv file, it tells me that it can't update it unless I open the source file. I *can't* take the time to open each csv - there will be hundreds. What am I missing wi...

make column lists for select query.
sheet1 table1 col1_1 table1 col1_2 table1 col1_3 table1 col1_4 table1 col1_5 table1 col1_6 table2 col2_1 table2 col2_2 table2 col2_3 table2 col2_4 .... .... sheet2 table1 col1_1,col1_2,col1_3,col1_4,col1_5,col1_6, table2 col2_1,col2_2,col2_3,col2_4, .... .... I want to make column lists for some table listed in sheet1. for example, select column_lists from table1 without vba is it possible? thanks. I think you may want some dependent lists. http://www.contextures.com/xlDataVal02.html HTH, Barb Reinhardt "kang" wrote: > sheet1 > table1 col1_1 > table1 col1_2 >...

New install
Someone in my area has called me and asked for help. He had a hard drive crash so he put in a new drive and reinstalled Media Center Edition. He accesses the internet through DSL and a router. Another computer is also plugged into the router and is working fine. On this computer, he has no internet access. Under the "status" tab it says "connected" but under the support tab the IP address, subnet mask, and gateway lines are all empty. If he unplugs the ethernet cable, nothing changes. It still says it is connected. Also, the icon in the tray that should appear...

Import Palm files
Can anyone help me by telling me how to import palm files (dat files) into outlook. I want to import contact, calendar, etc Depends what you can export to from Palm Desktop. Personally, I've found this messy. Why not just get sync software that can sync your Palm with Outlook directly? Chapura Pocketmirror or Pumatech Intellisync are two ideas....or your Palm CD may have come with this option. F. Carvalho wrote: > Can anyone help me by telling me how to import palm files > (dat files) into outlook. I want to import contact, > calendar, etc ...

Dates in a form for filtering Report query
I have a form "Period" with two text boxes. One for startDate and one for EndDate. I want to use that form to limit the query for my Report by the dates. However, when I refer to the Form it does not seem to understand it is a date? I use the following statement in my query: SELECT Opphold.CheckIn FROM Opphold WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate])); I also tried to convert it to a date like the following: CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work? What should I do in order to the query to read the condition or dat...

MS POP3 Connector
Hi All, I have configure the MS POP3 Connector to download the mail from our company external hosting mail server. But I want to leave a message in the mail server, but seem like the MS POP3 Connector will auto delete the message after successfully download it. Is there any way i can do it ahH??? -- Regards, Venedict ...

outlook 2000 help file
Seems after I did added SP3 from MS upgrade site my help from outlook has quit working. message is msohelp caused error in itss.dll. help works in ALL other programs including outlook address book. I'm running windows 98 SE, Office 2000 SB and IE 6. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.542 / Virus Database: 336 - Release Date: 11/18/03 ...

#Error Message
I have created a calculation to count the number of people and placed it in the Name Footer: =count([EyNumber]) and called the unbound box, CountNames. When there are no results it returns the #Error message. How can I remove this error message and display a message like, "No Data", or display the true results of a count? I have tried using the IsError function but can't seem to get it to work. Thanks Hi Nigel, Try: =IIf(IsError([EyNumber]), "No Data", Count([EyNumber]) Clifford Bass Nigel wrote: >I have created a calculatio...

"floating" date query
I have a tblPersonalInfo containing information including dtmHiredDate and strJobTitle. I have another tblPromotions containing info including dtmPromoDates and strPromoTitle. This table may contain duplicate strFirstName and strLastName because of different dtmPromoDates and strPromoTitles (because same person may have multiple promotions). Another tblJobTitle_JobRates lists strJobTitles and curJobRates. A final tblHoursWorked contains information including dtmPayEndDate. I am having difficulty writing a query that would extract dates based on HoursWorked with the appropriate JobTi...

Outlook should be able to email MS CRM advanced find queries (grou
The only way to e-mail a group in MS-CRM is to use the Quick Campaign function. The Quick Campaign has several disadvantages: - attachments for emails are not possible - this takes at least about 10 more clicks compared to sending a standard outlook email - the quick campaign doesn't have the look-and-feel of Outlook to which users are accustomed to Therefore it would be great if you could select the emailaddresses from groups retrieved from MS CRM into the TO:, CC: or BCC: field in a standard Outlook email message. Actually you would make the advanced find queries available to sel...

ESM error
I'm hoping someone can help me with this. I have Exchange2k3 SP2 running on a Win2k3 server. Our Exchange Admin is out on medical leave so I am resuming his responsibilities. My problem is I cannot access some of the storage groups using exchange system manager. We have 4 mailbox stores and I get an error trying to access 3 of them. The error is "An unknown error has occurred. ID no: 80040d1b Exchange System Manager". What is really puzzling is if I click on a storage group a second time, I don't get the error but the storage group shows up as empty. I have thi...

Debugger comes up when attaching files to Outlook 2002
I have a pro bono non-profit client running win xp sp3 & ms office pro. Last week she reported that every time she tried to attach a file to an outlook email, she received a dialog asking her if she wanted to start the debugger. It never allows her to attach the file and if she goes through with starting the debugger, she gets a variety of errors... never a repeating error. Anyone have any experience with this error? Michael ...

connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...

List Shows Private Files
DID ANYONE ELSE NOTICE THIS? Whenever I save an attachment, and go to the drop down menu for file name, I am shown a list of dozens of files recently accessed on the computer (not just files accessed by Outlook Express). This occurs even if recent cocuments is turned off in Windows XP and even if the recent documents folder for Windows XP has been cleared. This shows too much! Others using Outlook Express on the same computer can potentially view the file names of every document, image, and device recently accessed. Where is the folder to clear this data out of? duncan <anonym...

Memory Usage When Playing WMV HD Files
Hi all, When I watch a WMV HD file using WMP11, the file appears to start up and play well until I try to fast forward / jump to another part of the file, then the player just stops and after watching via the task manager, WMP memory usage goes through the roof ie. gigabytes and ultimately the player crashes. Have reinstalled the player, have tried various codecs etc but all appears to be unsuccessful. System is XP based, E6600 cpu with 4gb PC6400 memory and 8800GTS 640mb graphics. Any help would be much appreciated. Thanks in advance. Dumb question: what video card d...

To increase the upload file limit ONLY for one site collection
Hi, I have more than 1900 site collections on a web application. I know I can modify the upload file limit thru the CA or directly on the Web config file. However, I do not want to change the setting for all the site collections. I need to be able to change the setting only for a particular site collection. Is there anyway I can add manualy that entry in the web config file. Has anyone tried? Is this possible? Thanks in advance, Antonio ...

Format for Flashcards for MS Word & PPS 2003
Can anyone help me in sending me a Flashcard template so as to learn Chinese Characters, Pinyin, English. Compatible with MS Word 2003 & Power Point 2003. Any Tips or Ticks or general Knowledge in this area to improve learning through Flash Cards is more than Welcome. ...

svhost.exe: A CRM-related File?
Hello all. McAfee VirusScan has suddenly notified me that it detected a "potentially unwanted file" on the machine that I am using to host an ActionPack version of Microsoft CRM. This is an SBS installation, with everything running on one test machine. Here is the warning from McAfee: ---------------- Potential Unwanted Program Detected! The file C:\WINNT\System32\svhost.exe is a Potentially Unwanted Program (ServU-Daemon) ---------------- When I try to move the file to the desktop, I am alerted that it is in use and cannot be moved (no surprise, huh?). The interesting thin...

Combo Box Value List/Query Criteria
Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. I pass this combo box info into a query. Here's a sample of the query criteria: IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is Null,[Forms]![frmSwitchboard]![cboCaseType]) In other words, if the combo box selection is ALL, then display all records, otherwise use selected option. The above criteria does not work. Any suggestions? Thanks. Michael Michael, Try... (IsNull is an operator, not a value) IIf([Forms]![frmSwitchboard]![cboCaseType]="All", Null,[Forms]![frmSwitchboar...

Opening Files #5
When I first open Excel ( Office 2003) it goes to the wrong file. How can I get it to open intially the file I want ? Thank You -- George E To set up your main file in XL: <Tools> <Options <General> tab, And in the "Default File Location" box, Enter the path to whatever drive, and directory and file that you want. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George E...