Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Redwood City, California, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSQuery and Left Outer Join (Office XP)

    I am trying to use MSQuery to import data into Excel 2002 (running on WinXP) from a Visual FoxPro 6 database. I presume MSQuery is using ODBC, but I know little about MSQuery. When I try to adjust the join properties to change an inner join to a left outer join, both outer join options are grayed out. MSQuery help isn't. Crystal Reports will do left outer joins on the same data using ODBC.

    Is this possible with MSQuery? If so, any ideas on how to do it?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery and Left Outer Join (Office XP)

    If you have working SQL code of that query, AFAIK you can put sql code in msquery directly. Haven't got time to persue the details now.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Redwood City, California, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery and Left Outer Join (Office XP)

    Thanks, but there appear to be limits that I am exceeding. It is a 6+ table join with inner and left outer joins. It also has parameters. MSQuery does not seem able to digest the joins and parentheses used to to group them.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MSQuery and Left Outer Join (Office XP)

    I don't know of any reason why MSQuery wouldn't do what you want (other than the fact that it isn't). Can you include the required query in the FoxPro database itself, and then just retrieve the query, or does it need to be in the s/sheet?

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Redwood City, California, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery and Left Outer Join (Office XP)

    The query does not have to be in the spreadsheet, but it cannot be in the database. The database is part of a canned application that I cannot modify.

    The basic problem is to import data into a spreadsheet from the database based on a complex query with parameters. I was trying to do it with standard facilities from Windows and Office (no Access).

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery and Left Outer Join (Office XP)

    I guess you need to write VBA code to handle this.
    Then -using string concatenations- you might be able to construct this query as a normal query (no parameters) using the parameters values. Then pass the sql statement to the database using ADO. I do have some rather intricate examples, but not with joined table(s).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery and Left Outer Join (Office XP)

    Just a thought that may be well of the mark but could you create an intermediary database that contains all of the queries you need.
    This database could refer to your canned application and MSquery could access this view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •