SQL Query giving different results in VBA and Access

[jaishu]

Hi ,

I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.

[CODE BEGINS]
newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1..................
FROM TABLE A WHERE
NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0
ORDER BY A.MATCH_LEVEL DESC"

rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimistic
If rst1.EOF = False Then
Form_PHS_ASSIGN.Requery
Form_PHS_ASSIGN_SUB.Requery
Else
MsgBox " No records to Process!"
End If
[CODE ENDS]

EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..

Where am i going wrong..help pls!!!!

THANKS VERY MUCH!!

 

[Moniker]

You're using the wrong cursor type. adOpenDynamic will not give a record count. Without a record count, you cannot determine EOF. Go from this:

rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimistic

To this:

rst1.Open newquery, Cnt, adOpenKeyset, adLockOptimistic

The reason for this is that adOpenDynamic allows you to see deletes, changes, and adds from other users. Since the program can't read other user's minds, it has no idea what the record count is at any given moment. In a keyset, you can still see changes others make, but other changes (additions and deletions) are not visible until the recordset is closed and opened again. I should note that if another user deletes a record in a recordset you've opened as a keyset, you won't be able to edit it. However, when opening as a keyset, you basically get a snapshot view until it's closed and refreshed. That way, it takes in a finite number of records and deletions and additions won't show up until closed/reopened, so therefore, record count will work.

 

[jaishu]

You are right. I have used adopenkeyset in other part of the code somehow missed it here, , but even then the recordcount is 0 , when the query is actually returning more number of records, i dont know the reason for this ...

I tried with what u suggested but even then its not working as it is supposed to..
Is NOT exists creating a problem in VBA, ???

 

[Moniker]

That would be true. Instead of not exists, have you tried IsNull?

 

[unclejoe]

Hi,

I hope that's not a typo. Use the "*" instead.

Hi ,
NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0
ORDER BY A.MATCH_LEVEL DESC"

 

[jaishu]

Hi Guys,
Its working with * meant.. statement in subquery ....when i replaced ' ' with * in select statement...i just tried it, and EOF condition works correctly when i used it... VBA does things differently..it needs atleast a column to be specified even though its a query with EXIST...
Thanks very much for all of you who have helped me...
Thats great ! Thanks Unclejoe..........
Thanks so much once again!