How to copy data from PTQ to Access Table? (1 Viewer)

[usertest]

Hello,
I have the below code that I am trying to delete the records from access table (listtable) and then adding the records from SQL (SQL table)

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

Now, I would like to convert the below sql code into a PTQ and I think I can achieve that easily.

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 you see in the Do while logic in the 1st part of the code.
If you notice, the r1 (id) and r1(mgr_id) are copying the same column r(mgr_id)

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!

 

[Gasman]

Crossposted first at https://www.accessforums.net/search.php?searchid=712281

 

[The_Doc_Man]

The code is working fine and no issues in any part of it. I just want to improve the performance and optimize it.

It appears that you might be copying exact records from one table to another. But if you want to optimize performance, the fastest way is to not copy it, but just make a SELECT query that returns the values you want when you need them. If the first table is persistent, there is no need to copy anything. Now if the first table is NOT persistent, that might be trickier.

Note also Gasman's comment. It is considered impolite to post a question in multiple forums and not reveal that fact. It leads to us wasting our time on a problem that may have already been solved. Particularly since there is a login-wall on AccessForums.net and I'm not a member, there is the chance that someone HAS solved this for you and I would never know it.

 

[Minty]

I have provided answers in the original thread, but the OP isn't really taking them on board, or only picking the bits that they think will work, rather than grasping the concept of processing things in a query rather than RBAR.