?prevent MS query from changing * to detailed field names

Re: ?prevent MS query from changing * to detailed field name

Hi Tim,

Save your MSQuery as file and open it in Notepad or any text editor then remove the field names from SQL text and put * instead. Also remove the field names that would be listed after SQL text in file. Save query in text editor and quit.

Now double click on the query file. It should open in Excel by using new field name.

For example : the original MSQuery file was like below after creating it in Excel (your query would be similar):


After modifying it:


I hope it helps.
There is also another way to accomplish that by changing commandtext of query table in Excel before refreshing it via VBA. So if the above solution doesn't work for you then please let us know to suggest that way as well.

Suat

Re: ?prevent MS query from changing * to detailed field name

Thank Suat.

The question is, after you retrieve data into Excel sheet by directly clicking the external query file (the * query), the query's SQL text in sheet will be changed back to detailed field names. That's what I do NOT want (Right-click sheet to "Edit Query" to see SQL text changed).

In my case, users would like to click Excel file in which all required queries reside rather than click every query file. Actually I have ever created 9 different queries using what you mentioned way to let users click them one by one to retrieve all required business data from database. But they anyway don't like do things like that way.

What they expected are:

1. Setup only one Excel file in which all 9 queries reside, one sheet stores only one query data. As a result, they just need only one click on the Excel file rather than 9 queries;
2. Well format all the retrieved data, such use "Conditional formatting" to have all negative qty in RED.
3. All the data can be refreshed automatically when user open the Excel file and every 1 hour all data are refreshed again automatically.

In one word, they need one "Common platform" to work together.

Anyway, I think users' requirements are more reasonable and acceptable from business-running points of view. And I have already setup this Excel file so-called "common platform" to have about 20 uses to work together everyday and refresh the data at any time on their request via Excel built-in shortcut bar "External Data" > "Refresh All" even the Excel file is read-only for all users.

The only question is: why MS query always change the * to detailed field names back after I change detailed field names into * in SQL text?

I have to think it's one "BUG" of MS Query. I use Excel 2000. I am not sure how it works in the later versions.

Re: ?prevent MS query from changing * to detailed field name

As a short-term plan as you proposed the 2nd solution I have to create one add-in to let me and super-user to run it every week to update all 9 queries' SQL text in the "Common platform" excel file in case there's any fieldname in Access database changed (like week no. being changed with time-being).

Thank Suat again.

Re: ?prevent MS query from changing * to detailed field name

Another option -- you could use ADO instead of MSQuery. Takes a bit of setting up but there are plenty of posts on this board. One major advantage is that you define the SQL statement and feed it to the routine -- no changes from MSQuery helpfully updating your query string. Another advantage is flexibility. I use it in Excel and my users can select a range of parameters from drop-downs on a worksheet, to tailor the results to their requirements.

Denis

Re: ?prevent MS query from changing * to detailed field name

Thank Denis. Yes, that's one option. I can do it absolutely. But my users don't like it because:

1. They hate to see "Enable Macros" dialog when they open "common platform" Excel file. Even we can suggest users to re-set "Security level" to disable this diglog but it's anyway bring big risk to Excel users.

2. User don't like to click any non-built-in commands to retrieve business data. They said that MS Query have already had the great functionality to retrieve business data from Access database into Excel automatically on opening or every 1 hour (or any interval), why we do NOT take this big advantage?

In one word, if Excel MS-Query don't change * of SQL text to detailed field names, the everything become perfect.

I still wonder it's one "BUG" of MS query.

Re: ?prevent MS query from changing * to detailed field name

Hi Tim,

Yes I agree that the Enable Macros dialog is a minor hassle. If your users really don't want to deal with it, check out this post ... http://www.mrexcel.com/board2/viewtopic.php?t=85894 ... for a workaround

Denis

Re: ?prevent MS query from changing * to detailed field name

Hello Tim,

timwangTM said:

1. They hate to see "Enable Macros" dialog when they open "common platform" Excel file. Even we can suggest users to re-set "Security level" to disable this diglog but it's anyway bring big risk to Excel users.

Click to expand...

Sounds like a personal problem. I would not set my security to allow anything to come in without warning. Teach the people to hit Tab->Enter (this is actually pretty straight-forward). Or get a digital certificate as advised earlier...

timwangTM said:

2. User don't like to click any non-built-in commands to retrieve business data. They said that MS Query have already had the great functionality to retrieve business data from Access database into Excel automatically on opening or every 1 hour (or any interval), why we do NOT take this big advantage?

Click to expand...

Learn to use events then (although you'll probably create a 'denial-of-service' virus if you distribute this to just enough people):
http://www.cpearson.com/excel/events.htm

Want a procedure to run every hour, use a timed process:
http://www.cpearson.com/excel/ontime.htm

I'd hesitate to call this a bug, I'd call it a limitation. MS Query is a nice GUI and that's about it. Once you start to piece together what this GUI (I'm not calling this software, maybe an aplication of sorts...) is doing under the hood, you come to understand that it's probably not how you would develop an interface with OLE DB/ADO via VBA (if you know how).

MS Query does mind boggling things, it's not the exception, it's the norm. If you're serious about writing interfaces with Excel and DBs, my advice is to learn how to do it with OLE DB/ADO via VBA. There are three major benefits:

1) Better performance
2) More flexibility
3) More apparent self-documentation

All of which is pretty much what you want when comparing development choices.