speed up insert on ms access DB within a loop

[PinoyDev]

Good day!

I have a running code but I found it not very fast in terms of saving data. Is there a way to make it more faster? I am using msaccess as databse.

Set RsTrandet = New ADODB.Recordset
Sql = "SELECT TranDetail.AccountNo,cashflowcode, TranDetail.Narration, TranDetail.TranNature, " & _
      "TranDetail.TranAmount, Acctmaster.Accountname FROM TranDetail,Acctmaster where " & _
      "TranDetail.AccountNo = Acctmaster.Accountno " & _
      "and transactno = " & RsTranmain("transactno") & " order by serialno"
RsTrandet.Open Sql, SCNN, adOpenKeyset, adLockOptimistic
SCNN.Execute "delete from tran" & curtm
Slno = 0
LastSlno = 0
Set Rstemp = New ADODB.Recordset
Rstemp.Open "select * from tran" & curtm & " ", SCNN, adOpenKeyset, adLockOptimistic

SCNN.BeginTrans
Do While Not RsTrandet.EOF
   If RsTrandet("TranNature") = "C" Then
      Rstemp.AddNew
      Slno = Slno + 1
      Rstemp("Slno") = Slno
      Rstemp("Accountno") = RsTrandet("Accountno")
      Rstemp("Narration") = RsTrandet("Narration")
      Rstemp("Tranamount") = Round(RsTrandet("Tranamount"), 2)
      Rstemp("Accountname") = RsTrandet("Accountname")
      Rstemp("CFCode") = IIf(IsNull(RsTrandet("CashFlowCode")), 0, RsTrandet("CashFlowCode"))
      Rstemp.Update
   Else
      cmbAcctNo.Text = RsTrandet("Accountno")
      txtTotal.Text = Round(RsTrandet("Tranamount"), 2)
      txttotamt.Text = txtTotal.Text
      TxtCFCode = IIf(IsNull(RsTrandet("CashFlowCode")), 0, RsTrandet("CashFlowCode"))
      Call TxtCFCode_LostFocus
   End If
   RsTrandet.MoveNext
Loop
SCNN.CommitTrans

thank you!

[Santanu.Das]

& " order by serialno"
From which table you are taking that field. Please mention in SQL Statement. And also for cashflowcode in your SQL Statement.

= RsTrandet("Accountno")

Here also mention the table name, like TableNAme.Fieldname.

DO not understand Which condition is stored in the variable curtm.

Finally, vb is too much slow in database maintanence. If you have greter than 100 records it runs too slow.
From my opinion, you can use Insert Into Statement to add new record. Which can give you something better result.

[PinoyDev]

thanks Shark 1 for the response. I will try direct Insert Into, maybe it will help.

[Reverend Jim]

Slno always starts at one so I suggest you make this an identity field (auto-number) and do the inserts in one statement like

INSERT INTO table2 (Accountno, Narration, ...)
 SELECT Accountno, Narration, ... FROM table1 
  WHERE TranNature = 'C'
  ORDER BY serialno

You'd have to do some playing with this to get the exact query.

[PinoyDev]

tnx Reverend Jim!

Its a very good reference..