Module code running slow

[vani65]

Hi,
I am using the following code everything is working fine but is running very slow.Could anyone suggest how to improve.

thanks in advance

AddProgLog "ToGentrack - start"
Print #1, "HDR,GENTRACKUPDATE,ENCOMPASS,GENTRACK," & Format(Now, "dd/mm/yyyy,hh:mm:ss") & ",000000000,EncompassLite"

Set cnn = CurrentProject.Connection

sSQL = "SELECT * FROM FromGentrack " & _
"WHERE EXISTS (SELECT 1 FROM FromRegistry WHERE FromGentrack.ICP = FromRegistry.ICP AND FromRegistry.RType = 'STATUS')"
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic
While Not rs.EOF
LineNo = LineNo + 1
sSQL = "SELECT * FROM FromRegistry WHERE ICP = '" & rs!ICP & "' AND RType = 'STATUS' ORDER BY RDate, ID"
rsR.Open sSQL, cnn, adOpenStatic, adLockReadOnly
LDate = ""
LValue = ""
LValue2 = ""
While Not rsR.EOF
LDate = Delimit & ConvDate(rsR!RDate) & LDate
LValue = Delimit & Format(rsR!RValue, "000") & LValue
LValue2 = Delimit & rsR!RValue2 & LValue2
rsR.MoveNext
Wend
Print #1, "DET,STA," & rs!InstallNo & "," & Mid(LDate, 2) & "," & Mid(LValue, 2) & "," & Mid(LValue2, 2)
rsR.Close
rs.MoveNext
Wend
rs.Close

 

[JerryKlmns]

Dim curICP As String
Dim TheInstallNo 

Set rs = New ADODB.Recorset
With rs 
   .ActiveConnection = CurrentProject.Connection
   .Cursorlocation = adUseServer
   .CursorType= adOpenForwardOnly
   .LockType = adLockReadOnly
   .Source = "SELECT A.InstallNo, B.* " & _
             "FROM FromRegistry As B INNER JOIN FromGentrack As A ON " & _
                "A.ICP = B.ICP " & _
             "WHERE B.RType = 'STATUS' " & _
             "ORDER BY B.ICP, B.RDate, B.ID"
   .Open
   LineNo = 0
   While Not .EOF
       LineNo = LineNo + 1
       curICP = .Fields("ICP")
       TheInstallNo = .Fields("InstallNo") 
       LDate = Delimit & ConvDate(.Fields("RDate"))
       LValue = Delimit & Format(.Fields("RValue"), "000")
       LValue2 = Delimit & .Fields("RValue2")
       .MoveNext
       Do While curICP = .Fields("ICP")
          LDate = Delimit & ConvDate(.Fields("RDate")) & LDate
          LValue = Delimit & Format(.Fields("RValue"), "000") & LValue
          LValue2 = Delimit & .Fields("RValue2") & LValue2
          .MoveNext
       Loop
       Print #1, "DET,STA," & TheInstallNo & "," & Mid(LDate, 2) & "," & Mid(LValue, 2) & "," & Mid(LValue2, 2)
    Wend
    .Close
End With

Set rs = Nothing