Hi,
Below is the code
Code:
Public Sub GET_LIST()
Dim DC As New DataConnection
Dim r As New ADODB.Recordset
Dim r1 As New ADODB.Recordset
Dim strSQL As String
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From LISTTABLE;"
DoCmd.SetWarnings True
strSQL = "SELECT * FROM LISTTABLE;"
r1.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
strSQL = "SELECT DISTINCT A.MGR_ID, B.STATUS, B.MASTER, B.NAME AS REP_NAME, C.CODE, B.STATE FROM SQLTABLE AS A LEFT JOIN " & _
"SQLTABLE AS B on A.MGR_ID = B.ID LEFT JOIN SQLTABLE AS C ON B.MASTER = C.ID WHERE C.SUBSIDIARY = '001' AND C.STATUS <> '99';"
r.Open strSQL, DC.REP, adOpenKeyset, adLockOptimistic
Do While r.EOF = False
r1.AddNew
r1("ID") = r("MGR_ID")
r1("MGR_ID") = r("MGR_ID")
r1("STATUS") = r("STATUS")
r1("REP_NAME") = r("REP_NAME")
r1("CODE") = r("CODE")
r1("MASTER") = r("MASTER")
r1("STATE") = r("STATE")
r1.Update
r.MoveNext
Loop
r.Close
Set DC = Nothing
Set r = Nothing
Set r1 = Nothing
End Sub
In Summary, I delete all the records from the local access table (r1), and then I add the data from SQL table(r) to Access table(r1) based on conditions.
Now, I would like to convert the below sql code into a PTQ and I think I can achieve that easily.
Code:
strSQL = "SELECT DISTINCT A.MGR_ID, B.STATUS, B.MASTER, B.NAME AS REP_NAME, C.CODE, B.STATE FROM SQLTABLE AS A LEFT JOIN " & _
"SQLTABLE AS B on A.MGR_ID = B.ID LEFT JOIN SQLTABLE AS C ON B.MASTER = C.ID WHERE C.SUBSIDIARY = '001' AND C.STATUS <> '99';"
Once I do that, How do I code so that the condition of r1(id) = r(mgr_id) and r1(mgr_id) = r(mgr_id) that is part of the logic?
I'm stuck as to how do I code that part of the logic?
The code is working fine and no issues in any part of it. I just want to improve the performance and optimize it.
Thanks!