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