SELECT with complex WHERE bombs with error code 0xc0000005 1

[DENewkirk]

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?

 

[cheerio]

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.

 

[DENewkirk]

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.

 

[DENewkirk]

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!!!

 

[MarkSweetland]

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.

[small]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 = ?
	)
[/small]

Mark

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

 

[cheerio]

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.