Inserting data using PTQ into local access table

[usertest]

Hi,


Below is the 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.

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!

[Minty]

You don't need the loop or the recordsets you could simply run an update query?
This was written on the hoof so test it for syntax first:

Something like

UPDATE LISTTABLE 
SET 
        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
FROM Listtable r1
Join 
(    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'
) AS r 
ON r.mgr_ID On r1.id ANd r1.MGR_ID = r.mgr_ID

[usertest]

Where do I put that query?
Cause there is access table and SQL table being used in the query.
I would have to reference the database connection for my SQL table?

You don't need the loop or the recordsets you could simply run an update query?
This was written on the hoof so test it for syntax first:

Something like

UPDATE LISTTABLE 
SET 
        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
FROM Listtable r1
Join 
(    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'
) AS r 
ON r.mgr_ID On r1.id ANd r1.MGR_ID = r.mgr_ID

[Minty]

Just link to the SQL table, then use it in your query.
You can't pass through the Access table it will have to be done in Access, so you may as well use a update query which will be 100 times faster than a Loop

Avoid RBAR (Row By Agonising Row) at all costs.

Actually, thinking about it, if your local table is pulled from a SQL Table then just run the whole thing as a Pass through.

[usertest]

Hi,
When I tried to put the query as PTQ, it shows an error ODBC error - call failed. Invalid object name LISTTABLE.

Just link to the SQL table, then use it in your query.
You can't pass through the Access table it will have to be done in Access, so you may as well use a update query which will be 100 times faster than a Loop

Avoid RBAR (Row By Agonising Row) at all costs.

Actually, thinking about it, if your local table is pulled from a SQL Table then just run the whole thing as a Pass through.

[Welshgasman]

Now crossposted at https://www.access-programmers.co.uk...-table.329106/

[Minty]

You aren't reading the answers very well.

Just link to the SQL table, then use it in your query.
You can't pass through the Access table it will have to be done in Access, so you may as well use a update query which will be 100 times faster than a Loop
.

If ListTable doesn't exist in your SQL database of course you can't pass it through to the server.

If it does you can.

[zpy2]

update test1
set test1.name=test2.name,test1.age=test2.age
from test1
inner join test2
on test1.id=test2.id

ms sql server ?

[usertest]

sorry for the late reply,
But LISTTABLE is just a local access table and does not exist on SQL database. So agreed, I cant pass that as PTQ.

You aren't reading the answers very well.


If ListTable doesn't exist in your SQL database of course you can't pass it through to the server.

If it does you can.

[pbaldy]

I feel like this has already been given in one of your various threads, but your query would be:

INSERT INTO LISTTABLE(Field1, Field2, ...)
SELECT Field1, Field2, ...
FROM PTQ

[NoellaG]

You can't pass through the Access table it will have to be done in Access,

actually, it can be done in SQL if you use SSIS

[Minty]

actually, it can be done in SQL if you use SSIS

Well, yes it can, but I doubt the OP would want to that in the middle of their normal form operations.

He's trying to make an efficiency improvement, when a simple query based solution locally in Access would be massively faster than looping through a recordset.

[NoellaG]

You're right, and as a SQL DBA I love you for taking the OP away from RBAR operations. Thank you