Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query not Seen in ASP (Access 2003, ASP Classic, IIS 7.0)

    Not sure if this is the right forum, but I think the problem is more Access than ASP but could be wrong... On a webproject, I use a Union query to list columns across two tables to do a look-up. Strangely enough, attempts to select the query fail in ASP. The error is the object cannot be found.

    I can work-around this by copy-pasting the SQL behind a fake Select statement to the existing Union query into the Select string so the problem is solved in one sense but I was curious if there's something about Union queries that is special & if so if there's any way to get around this so-to-speak specialness. FWIW, the asp select statement is a fully-qualified SQL string that does in fact reference the Union query, so I am not re-running the Union logic in ASP. I cannot do things like SELECT Count(*) FROM MisterUnionQuery, AFAICS.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query not Seen in ASP (Access 2003, ASP Classic, IIS 7.0)

    Since this question has remained unanswered in the Access forum for almost three weeks, I'll move it to the Web Design, Coding and Scripting forum to see if the experts there have a suggestion.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query not Seen in ASP (Access 2003, ASP

    Hi Steve

    Have you tried counting the primary key(PK) field or if not a PK a non-nullable field, like so:

    SELECT Count(PK) as intCount FROM MisterUnionQuery

    If not you could try to create the union query from within the asp
    Jerry

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Union Query not Seen in ASP (Access 2003, ASP

    I have used SELECT against union queries in MS Access itself, so I don't think MS Access is incapable.of doing this. However, I can't recall running a query against an MS Access query, as opposed to a table, from ASP. If it works with a stored "standard" query as the FROM source, I can't see why it wouldn't work equally well with a stored union query. Unfortunately, no way to test at the moment...

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query not Seen in ASP (Access 2003, ASP

    I don't have the project in front of me, so I can't say for sure but I probably just ran Select Count(*) on Query_usin_UnionSelect SortBy WHERE Email = "Email from Webform" DateEntered DESC. Nothing specific to PK fields or indexed columns, etc.

    I wanted to use Union as I wanted a quick way to look in two different tables for the existance of an e-mail - not important which one might have it or even if there's more than one instance of an e-mail, just know if it exists, for starters. As you can see from the psuedo-code, I also want to sort any existing Email matches to list the most recent one to run an Expriry code section when the date found is "expired".

    The curious thing is, as my post indicates, ASP can't find the object Query_using_UnionSelect itself, but if I "unpack" the query and run the SQL code...works.

    So, I don't have a problem - it was solved - but it strikes me as weird that I have to do this and wanted to know if there's a "rule" Access or ASP is following that I am unaware of or is this something of a bug.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query not Seen in ASP (Access 2003, ASP

    I've run ASP code against Access queries lots of times. never had a problem before.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Union Query not Seen in ASP (Access 2003, ASP

    I just ran a little test where I converted the query behind a page into a Union query, and the page still worked OK.


    strSQL = "SELECT qryItemsforSale2.* from qryItemsforSale2 order by ItemID ;"

    qryItemsforSale2 is a Union Query, so there is no rule that says you cannot use Union Queries.
    Regards
    John



Posting Permissions

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