Advice on MS Query

[hrm1220]

I have an MS Query that I'm hoping I can get advice on for Excel 2003. What happens in excel is that the user puts in the criteria in certain cells, then the VBA opens up Business Objects, runs the query then saves the file as a text tab delimiter. After this it pulls that data into the MS Query and this is all that I have so far. I'm hoping to get some direction on best methods for my next steps.

1. Can mutliple users access the MS Query at the same time?
2. Can you change the text file using code? (it will change every time the user access the query but the field names will never change)
3. Can you create a formula inside the MS Query?
4. Can you loop through the MS Query without pulling the data into Excel since there can be upto 1Million rows?
5. If this is a slower method, what would you recommend to use? The users want to use Excel since this is the program they know best.

Any advice would be greatly appreciated.

 

[SkipVought]

Hi,

1. Can mutliple users access the MS Query at the same time?

MS Query is an application that is loaded on each user's PC.

2. Can you change the text file using code? (it will change every time the user access the query but the field names will never change)


I almost never use saved queries. The query CommandText is embedded in the Excel sheet. I do modify the CommandText, also referred to as SQL, using VBA, quite easily.

3. Can you create a formula inside the MS Query?

What kind of formula. Some functions are available in MS Query.

4. Can you loop through the MS Query without pulling the data into Excel since there can be upto 1Million rows?

If you use the proper criteria, you can often narrow down the resultset to fit on a sheet, since MS Query only returns data to an Excel sheet. However, you can also use ActiveX Data Objects, return a recordset and loop thru the recordset.

5. If this is a slower method, what would you recommend to use? The users want to use Excel since this is the program they know best.

It really depends on the details of your process. I employ both MS Query and ADO, depending on the requirements.

Now that you have a querytable embedded in your sheet, try this, for grins.

sub test
  with YourSheetObject.querytables(1)
    debug.print "the connection:"
    debug.print .connection
    debug.print "the SQL:"
    debug.print .commandtext
  end with
end sub

where YourSheetObject is the sheet object containing your querytable, like Sheets(1) or Sheets("Sheet1"), and open View > Immediate Window


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[xlbo]

Just to confirm what Skip says but also to state that I used to use MS Query a lot. I now tend to use ADO as it is more flexible when working with recordsets...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I agree with Geoff about ADO. I most often use MS Query for quick hits and testing my SQL, and then ADO for running queries, especially for constructing functions to return a table value.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}