INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SELECT with complex WHERE bombs with error code 0xc0000005

SELECT with complex WHERE bombs with error code 0xc0000005

(OP)
I have a Visual Basic application with a very complex search page, most of which works exactly as planned.  One piece of the SELECT statement, simplified for testing, reads thus:

[SELECT #1]
SELECT STRUC_ID, DRWG_NO, STRUC_NM, STRUC_TYP_CD
FROM dbo_STRUC
WHERE (dbo_STRUC.STRUC_ID IN (SELECT STRUC_ID FROM
     dbo_STRUC_WRK WHERE dbo_STRUC_WRK.STRUC_WRK_NO IN
     (SELECT STRUC_WRK_NO FROM dbo_STRUC_WRK_CALCBOOK
      WHERE dbo_STRUC_WRK_CALCBOOK.CALCBOOK_NO


[This piece looks for "Structures" sharing a given "CalcBook".]

I have another source of Structure/CalcBook relations, so to broaden the search I want to add this piece (again, formulated for testing):

[SELECT #2]
SELECT STRUC_ID, DRWG_NO, STRUC_NM, STRUC_TYP_CD
FROM dbo_STRUC
WHERE (dbo_STRUC.STRUC_ID IN (SELECT StructureNo FROM
       Structures WHERE Structures.CalcBook=?))


I have tested each piece separately, and have gotten the list of Structures that share the input CalcBook.  However, when I combine SELECT statements #1 and #2, the SELECT statement bombs with an "attempt to read protected memory" error 0xc0000005, whether in MS ACCESS [which crashes completely] or in VB 2005 (ADO code or DataSet Designer) [VB traps the error], and whether I use a parameter for the '?' or a hard-coded value.

The blended filter piece looks like this:

[SELECT#3]
SELECT STRUC_ID, DRWG_NO, STRUC_NM, STRUC_TYP_CD
FROM dbo_STRUC
WHERE ((dbo_STRUC.STRUC_ID IN (SELECT STRUC_ID FROM
        dbo_STRUC_WRK WHERE dbo_STRUC_WRK.STRUC_WRK_NO IN
        (SELECT STRUC_WRK_NO FROM dbo_STRUC_WRK_CALCBOOK
         WHERE dbo_STRUC_WRK_CALCBOOK.CALCBOOK_NO=?))) OR
       (dbo_STRUC.STRUC_ID IN (SELECT StructureNo FROM
        Structures WHERE Structures.CalcBook = ?)))


I've counted parentheses a hundred times, and have just about scratched my head bald over this one.  I'm sure it must be a simple DUH!, but haven't got a clue (I did recently get new glasses...).  Does anything obvious pop out?

RE: SELECT with complex WHERE bombs with error code 0xc0000005

Some ideas:

Where is the data - dbo_ usually comes from SQL server which handles complex queries better.  If it is on a SQL Server you could use a Pass through query to get the server to do the work.  Otherwise Access has to pull all the data back from the server so that it can test your complex criteria.

The brackets balance.

Instead of using OR you could try a UNION query.

It may be that data volumes would reduce on the inner selects if you had the DISTINCT keyword.

If you must work in Access consider creating a temporary table to hold the IDs which you could populate with a series of simple update queries.  You could then have a very simple join on that temporary table.  However, this is harder if multiple users could be running similar queries at the same time.

RE: SELECT with complex WHERE bombs with error code 0xc0000005

(OP)
Thanks, cheerio.  I will try the UNION query.

I'm not really working in Access.  I'm programming VB 2005, but in order to do cross-db queries I'm using an Access DB to link tables from several sources--some SQL server and some Access.  I'm also cross-testing directly in the Access linked DB to see whether there is a difference between .NET and Access, and perhaps I've some bad habits that I need to unlearn.

RE: SELECT with complex WHERE bombs with error code 0xc0000005

(OP)
Hi, again, cheerio.  I have so far tried the UNION statement with just this piece of the query, and it works great!

Next, I will put the UNION at the tail of my long SQL statement in the full search query and test that out.  I'll try to reply again as soon as I've had a chance to break away from other tasks....

Thanks again!!!

RE: SELECT with complex WHERE bombs with error code 0xc0000005


I've never been terribly fond of using IN.  You can rewrite the statement using EXISTS which, at least, reads a bit easier.  You can also tack on additional EXISTS as you need.

CODE

SELECT STRUC_ID, DRWG_NO, STRUC_NM, STRUC_TYP_CD
FROM dbo_STRUC a
WHERE EXISTS (
    select *
    from dbo_STRUC_WRK b
        INNER JOIN dbo_STRUC_WRK_CALCBOOK c on c.STRUC_WRK_NO = b.STRUC_WRK_NO
    WHERE a.STRUC_ID = b.STRUC_ID and c.CALCBOOK_NO = ?
    )
    OR EXISTS (
        SELECT *
        FROM Structures d
        WHERE d.STRUC_ID = a.STRUC_ID and d.CalcBook = ?
    )


     Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

RE: SELECT with complex WHERE bombs with error code 0xc0000005

Thanks for the star.

The Access database engine is limited in the complexity of the queries it can handle.  Sometimes a little lateral thinking is required to work within its limits.  Mark's suggestion is another example - although the IN and EXISTS are logically equivalent one may be more efficiently implemented by the database engine.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close