Access queries on AS400

[Ujagar]

This is something that I find a bit strange in my application:

Until now to fulfill my Access database, I have Excel files that were making requests on a DB2-AS400. These files are linked in Access, I created the Real Access tables to use the given (by means of requests to create tables). These Excel files were updated once every morning for about 10 minutes.

In recent days I discovered the query, and I find it a bit nicer because it's all done "in" Access, no need of Excel files. The only worry now is that duration of upgradation has increased to 50 minutes! Is this normal?

My "real" Access tables are linked to SQL Server tables ...

I have two types of actions:

DoCmd.OpenTable "T_cb_complete", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.RunCommand acCmdCloseWindow
DoCmd.OpenQuery "R_cr_T_cb_complete", acViewNormal, acEdit

Which is made several times, and whose query "R_cr_T_cb_complete", that is an Access table is based on some query, that is to say, I am given the multiple tables AS400 and mergers in 1 Access table.

I also have 3 blocks when I use the ADO:

SET rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Source = "SELECT * FROM T_accounts_full WHERE NBJ_DEC>20 AND GSTR_C<>'GPA'"
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open
IF NOT rs.EOF Then
    rs.MoveFirst
    While NOT rs.EOF
            SET rs2 = New ADODB.Recordset
            rs2.ActiveConnection = CurrentProject.Connection
            rs2.Source = "SELECT * FROM T_20J_dem WHERE account ='" & rs![account] & "'"
            rs2.CursorType = adOpenDynamic
            rs2.LockType = adLockOptimistic
            rs2.Open
            IF rs2.EOF Then
                rs2.AddNew
                rs2![AG] = rs![AG]
                rs2![account] = rs![account]
                rs2![DOSS_VU] = False
                rs2.UPDATE
            End IF
            rs2.Close
        rs.MoveNext
    Wend
End IF
rs.Close
 
SET rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Source = "SELECT * FROM T_20J_dem"
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open
IF NOT rs.EOF Then
    rs.MoveFirst
    While NOT rs.EOF
            SET rs2 = New ADODB.Recordset
            rs2.ActiveConnection = CurrentProject.Connection
            rs2.Source = "SELECT * FROM T_accounts_full WHERE account ='" & rs![account] & "'"
            rs2.CursorType = adOpenDynamic
            rs2.LockType = adLockOptimistic
            rs2.Open
            IF rs2.EOF Then
                rs.DELETE
            Else
                IF (rs2![GSTR_C] = "GPA") Then rs.DELETE
            End IF
            rs2.Close
        rs.MoveNext
    Wend
End IF
rs.Close

Where I put the missing records in the table "T_20J_dem" and then I delete those that do not matter ...
And this is where I think I have taken a significant time ...

[kelfro]

Do you know what stage it take you long? You say it takes 50 min but where? Put the timestamp in your code before and at the end of a block, to know it or train. Based on this we will be able to focus on the motion that drags the whole thing is to know which one. Can you provide this info?

[Ujagar]

After the try, the longest part of the code is

SET rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Source = "SELECT * FROM T_ch_de4a5"
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open
IF NOT rs.EOF Then
    rs.MoveFirst
    While NOT rs.EOF
            SET rs2 = New ADODB.Recordset
            rs2.ActiveConnection = CurrentProject.Connection
            rs2.Source = "SELECT * FROM checkbook WHERE account='" & rs![account] & "'"
            rs2.CursorType = adOpenDynamic
            rs2.LockType = adLockOptimistic
            rs2.Open
            IF rs2.EOF Then
                rs.DELETE
            Else
                IF ((rs![PRECHQ] <> rs2![PRECHQ]) AND (rs![DERCHQ] <> rs2![DERCHQ])) Then rs.DELETE
            End IF
            rs2.Close
        rs.MoveNext
    Wend
End IF
rs.Close

which take 44mn!

where "checkbook" is the direct SQL query "T_ch_de4a5" is a linked SQL Server table, which as I have put new recordings (part 1) and delete those that are obsolete (second part).

I put the query:

SELECT cast(agemanagement AS smallint) AS mag, nocmpt AS account, cast(substr(dtdem, 1, 4)||'-'||substr(dtdem, 5, 2)||'-'||substr(dtdem, 7, 2) AS date) AS dtdem, cast(substr(dtrefa, 1, 4)||'-'||substr(dtrefa, 5, 2)||'-'||substr(dtrefa, 7, 2) AS date) AS dtrefa, cast(substr(dtdeli, 1, 4)||'-'||substr(dtdeli, 5, 2)||'-'||substr(dtdeli, 7, 2) AS date) AS dtdeli, cast(substr(dtannu, 1, 4)||'-'||substr(dtannu, 5, 2)||'-'||substr(dtannu, 7, 2) AS date) AS dtannu, etchqr
 
FROM pack10pdta.chqchqp000
 
WHERE dtdeli=' ' AND dtrefa<>' ' AND dtannu =' '
 
ORDER BY agemanagement, nocmpt

[kelfro]

Already in your code I do not understand something. You declare two cursors. You read the first reading for a second. What about a join between two tables? T_ch_de4a5 and checkbooks are well on the AS/400? You are in which version of OS/400?

[Ujagar]

The table "T_ch_de4a5" is a SQL Server linked table (I treat it as an Access table). The table "checkbook" is in AS400. The version I'm not sure ...

The trick is to go through all the records "T_ch_de4a5" and see what are the table "checkbook". If they are not (<if rs2.EOF then rs2.delete>), I erase it.

I'll think a bit to try to make a query with a join.

[kelfro]

Assure yourself that there is an index on the area ACCOUNT checkbook, if launching a CREATE INDEX on file checkbooks. But if an index is created and the time is still long, looks rather later SQL Server because between DB2 and SQL Server that does not box in the same category, if that stick is bound to SQL SERVER