Critical DAO OpenRecordset Method Error in VB6

[Mokil]

Hey gang, thought I'd better let you know about this critical DAO error in VB6 with an Access 97 database...I'm currently trying to find a way to solve it, but someone please let me know if they have a solution!!

Situation: I'm currently developing a commercial app, which contains it's own customised report generator. Users can specify criteria etc.

Now assume I have two tables - "Cases" and "People".

Because of the open nature of the report generator, a user can generate absolute crap SQL - such as:

Select Cases.Key, People.Age, People.Gender FROM Cases, People, WHERE People.Gender = &quot;Female&quot; AND People.Age >= 1 AND People.Age <= 10 OR People.Age >= 11 AND People.Age >= 20 ORDER BY People.Gender ASC
[\code]
Told you it was rubbish!

Anyway, assume there's around 760 records in the &quot;Cases&quot; table, and around 900 records in the &quot;People&quot; table.

Obviously you're going to get a Cartesian product without creating proper joins - which is not a problem, as I've got checks in for this kind of thing.

The problem is, when this code executes the OpenRecordset method, as similar to below:
[code]
Set TABLE NAME = Database.OpenRecordset(SQL IN HERE, dbOpenDynaset)
[\code]
VB Freezes - it stops completely dead, and a CTRL-ALT-DEL is required.
It doesn't matter what table type you use or what options you use - dbSnapShot, dbReadOnly etc - you still get the freeze.

If the code does execute, it returns over 490,000 records!!

What I don't get is, just opening the recordset should not cause this problem - DAO should just open the recordset, and I should then be able to use recordcount, absoluteposition etc etc to do my checks.

I tried running this query in MS Access, using the exact same SQL, and MS Access also freezes in the same way as VB does.

Strange eh...I'll investigate and I will find a fix eventually, but if anyone knows anything please let me know!

Cheers gang.

Later,
Mokil.

 

[Mokil]

Yo all, an update on my post.

After much investigation, messing about, it turns out the query was not crashing - just taking a heck of a long time to run!

Bear in mind this was on an Athlon 750 with 512MB RAM and 45GB UDMA100 Disk.

Anyway, the query eventually returns a 3183 error - which is Not enough space on temporary disk.

Temp disk location is based on the TEMP DOS environment variable. This has only been tested on Win 98 SE so far, get the feeling Win ME and 2000, with their limited versions of DOS, may well have a major problem here - depends how good MS's emulation is.

At least it isn't crashing the system. Next step is to try and find out this is going to happen BEFORE query begins execution - otherwise user has to sit there for 20 minutes on an Athlon 750 - don't know what it would be on a 32MB P200!

Cheers,
Mokil.

 

[TheInsider]

Maybe I'm tired because it's the end of the day, but I think the problem here is that you aren't specifying a join between the two tables. Without an inner, left, or right join you can expect to get 760 * 900 records. Rob Marriott
rob@career-connections.net

 

[Mokil]

Hey there CC.

Yes, I know there is no left/right/inner join etc - the report generator can create this, but as I said in the first e-mail, in the interests of keeping the user's ability to generate SQL as open as possible, they can generate complete rubbish (as above)!!!!!

The problem lies not within the cartesian being returned by the rubbish SQL statement, but with the fact that the OpenRecordset method takes over 20 minutes on a high-spec machine to return the records.

I have loads of checks in the code for cartesian's etc, but I need to have the number of records returned by OpenRecordset before I can use these.

As usual, I think MS is to blame - this happens in Access as well, just using a standard query!!!

What I need to do is attempt to simulate the SQL without actually running it, so I can determine if a cartesian applies.

Thinking on BTW, I've also run the SQL in my first post WITH the relevant joins on the PKs and FKs - and it STILL doesn't complete, I still get a 3183.

The problem in the SQL lies with the poor AND/OR criteria specified...but this still doesn't get around the fact that all OpenRecordset should do is open the recordset virtually instantly, not take years whilst it counts the records!!!

Cheers CC.

Later,
Mokil.

 

[TheInsider]

I'll have to agree with you there. A long time ago, when I was new to Access and didn't know the shorcuts and tricks that I know today, I was asked to write a complex report that rolled up alot of information. This report's record source query consisted of roughly 32 sub-queries, in which a few of those sub-queries had sub-queries. When I ran this query for the first time, I found that it seemed to hang - like you had found. I then decided to run the query again at the end of the day and see if it would be finished the next morning.

The next morning it was still running. I didn't know what to do, as this was my best attempt at writing this query at the time. I then found that if I broke up those 32 sub-queries into a set of 4 queries, each contianing 8 subqueries, and then placed the 4 queries into the main query, that I could get my results in ~30 seconds!

Since I hadn't actually re-written any of the 32 sub-queries, I was pretty amazed. This was the first time I had run into this. I find that Access 97 is famous for this type of behavior -- it appears frozen, even says [Not Responding] in the task monitor, while it is actually processing your code or query in the background, but doesn't allow you a way to cancel out. I find that Access 2000 is a little more powerful but has its problems too. Rob Marriott
rob@career-connections.net

 

[Mokil]

Hi Rob, sorry I've been away at meetings etc for the last week or so and have only just seen your post.

Glad to see it isn't just me who has come across this!!

I've got another little story...I took a job as a programmer with a small firm, and one of my first jobs was to sort out a Sage link into Access. The organisation concerned had Sage tables linked into Access 97 via ODBC.

No big deal, but every month they ran a report - the report took over 2 DAYS to run, on a K6-2 450 (High spec at the time!)!!!!

Unfortunately the guy before me was a clown and not a programmer. I cleaned up the code and the SQL etc, but it was still slow, mainly because of the OpenRecordset issue, and of course the ODBC.

I wrote a little routine to copy every Sage record into a local Access table set up the same way, then run the report based on that.
What happened...a report that took over 2 days to run suddenly ran in 3 minutes...now that's progress!!!

Cheers,
Mokil.