Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Can your copy of Access run this query?

    Last week, in another thread here, a number of folks wrote that MS-Access crashes when they use a sub-Query in the GUI. Maybe I'm missing something, but I do it all of the time. In fact, I create very few MDB files without sub-queries, and I've never created an ADP without one.

    The attached file is a zipped MDB with the table from that previous thread, and my query solution. I'd appreciate it if a few folks would check to see if this works on your installation of MS-Access.

    Thanks!
    -PatP
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    skipping: My Documents/SubQuery.mdb: this file is not in the standard Zip 2.0 format
    Please see www.winzip.com/zip20.htm for more information
    i probably wouldn't have been able to open the mdb anyway

    i've only got access 97

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    25
    I have acces2003, and I can't open it

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Access2003 SP1 here and works fine. GUI looks good and query runs as expected.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Timmieee
    I have acces2003, and I can't open it
    Just curious, but what happens when you try to open it (error dialog, computer hangs, blue smoke comes out the back, etc)?

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by cpgospi
    Access2003 SP1 here and works fine. GUI looks good and query runs as expected.
    Ok, so I'm not completely off the wall (at least on this particular issue).

    I regularly distribute some of these files to lots of users, and I've never had a complaint. I just wanted to be a bit more sure that I wasn't leading them down the primrose path to oblivion, although I can't imagine that some of them wouldn't have made it quite clear if things didn't work as they'd expected.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any chance you could save your mdb as "previous version" and post another zip (assuming you have some zip software that actually knows the zip 2 standard)

    i'd love to give it a go in access 97, just to prove that subqueries work just fine there too (which i already know, but i wanna see your query)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I opened this in AccessXP and it worked fine.
    I converted it back to Access97, and it worked okay there, too.

    I use subqueries like that all the time also, and have never had a problem
    (other than bad syntax).
    Inspiration Through Fermentation

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Rudy: Will do, but that is a tonight thing for me... I don't have either the MDB file itself, a 2.0 compliant zip tool, or permission to use either of them at this location.

    RedNeckGeek: Thanks for the feedback!

    -PatP

  10. #10
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Here's 97 version. Hopefully you can uncompress it....

    Rudy, I had that same ZIP error a few days ago. I upgraded from Winzip 8 to Winzip 9.0 and that did the trick.
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since we haven't heard any more from Rudy, I'll assume that he's still having problems with the zip format. I've used PKZIP to zip an Access-97 version of that MDB, and attached it here to make things simpler.

    -PatP
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, no additional trouble, just somehow in all the excitement yesterday i either overlooked or didn't get the email notification that this thread had been updated

    tried both your zip files, both worked, both databases opened up nicely, and both queries ran lickety-split, badda-boom, bob's your uncle, and what was the original question again?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The other thread that I linked to in my first posting in this thread is what started everything.

    More than one poster in that thread indicated that any Access (actually Jet) query that used sub-queries caused their copy of MS-Access to crash. That confused me, since I use sub-queries in nearly evey bundle of SQL I write!

    I wanted to confirm that I wasn't a "one trick pony" and that other people could open and use a query that contained sub-queries.

    -PatP

  14. #14
    Join Date
    Feb 2005
    Posts
    25
    Quote Originally Posted by Pat Phelan
    Just curious, but what happens when you try to open it (error dialog, computer hangs, blue smoke comes out the back, etc)?

    -PatP
    I wish it did something. But just.. nothing happened.

  15. #15
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Pat,

    The only thing i have to note is that your 2 queries are slightly different,
    in 1 you referenced tableA as a
    and in the attached you Left SubqueryTable as is

    the GUI on the inital query couldnt recognise that TableA was what was being queried and gave me a blank a to work with (this is my only thought as to the errors occuring)

    Appart from that i didnt have any errors using Access 2002 SP2

Posting Permissions

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