tkrpata
asked on
Need to speed up my code.
I have a table called MTNSUSP and another on that I do a lookup in called tblResponsible, and what I am doing is adding a field called Resopnsible to the MTNSUSP table and then looking up the value in the Code and CusType fields of the MTNSUSP table and getting the corresponding value from the table tblResponsible and putting that in the associated record in the field "Responsible" in the MTNSUSP table. Problem is with over 1200000 records it takes about 2 hrs. Any way to speed it up?
Private Sub Command27_Click()
Dim dbs As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim MtnCode As String
Dim MtnCusType As String
Dim ResCode As String
Dim ResCusType As String
Dim ResResponsible As String
Dim f As Field
Dim td As TableDef
Me.Visible = False
DoCmd.OpenForm "frmPleaseWait1"
Set db = CurrentDb
Set td = db.TableDefs("Mtnsusp")
Set f = td.CreateField("Responsibl e", dbText, 20)
td.Fields.Append f
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Mtnsusp ")
Set rst1 = dbs.OpenRecordset("tblResp onsible")
rst.MoveFirst
Do While Not rst.EOF()
MtnCode = rst!Code.Value
MtnCusType = rst!CusType.Value
rst1.MoveFirst
Do While Not rst1.EOF()
If rst1!DisCode.Value = MtnCode Then
If rst1!CusType.Value = MtnCusType Then
rst.Edit
rst!Responsible.Value = rst1!Responsible.Value
rst.Update
GoTo BEGINAGAIN
Else
rst1.MoveNext
End If
Else
rst1.MoveNext
End If
Loop
BEGINAGAIN:
rst.MoveNext
Loop
DoCmd.Close acForm, "frmPleaseWait1"
End Sub
Private Sub Command27_Click()
Dim dbs As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim MtnCode As String
Dim MtnCusType As String
Dim ResCode As String
Dim ResCusType As String
Dim ResResponsible As String
Dim f As Field
Dim td As TableDef
Me.Visible = False
DoCmd.OpenForm "frmPleaseWait1"
Set db = CurrentDb
Set td = db.TableDefs("Mtnsusp")
Set f = td.CreateField("Responsibl
td.Fields.Append f
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Mtnsusp
Set rst1 = dbs.OpenRecordset("tblResp
rst.MoveFirst
Do While Not rst.EOF()
MtnCode = rst!Code.Value
MtnCusType = rst!CusType.Value
rst1.MoveFirst
Do While Not rst1.EOF()
If rst1!DisCode.Value = MtnCode Then
If rst1!CusType.Value = MtnCusType Then
rst.Edit
rst!Responsible.Value = rst1!Responsible.Value
rst.Update
GoTo BEGINAGAIN
Else
rst1.MoveNext
End If
Else
rst1.MoveNext
End If
Loop
BEGINAGAIN:
rst.MoveNext
Loop
DoCmd.Close acForm, "frmPleaseWait1"
End Sub
you can user single Sql to do that
UPDATE Mtnsusp INNER JOIN tblResponsible ON (Mtnsusp.Code= tblResponsible.DisCode) AND (Mtnsusp.CusType= tblResponsible.CusType) SET Mtnsusp.Responsible= tblResponsible.Responsible ;
as i understand
and not it better and faster if yo made Code,DisCode,CusType indexed in the table design it may take space but it well speed you up
UPDATE Mtnsusp INNER JOIN tblResponsible ON (Mtnsusp.Code= tblResponsible.DisCode) AND (Mtnsusp.CusType= tblResponsible.CusType) SET Mtnsusp.Responsible= tblResponsible.Responsible
as i understand
and not it better and faster if yo made Code,DisCode,CusType indexed in the table design it may take space but it well speed you up
sorry i was a\writing my comment same time with alexgud i didn't see his comment
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nico,
I'm not that familiar with SQL, how would I implement that code, via a procedure or a function, and how would it be structured. Thanks for all your help in advance.
Tom
I'm not that familiar with SQL, how would I implement that code, via a procedure or a function, and how would it be structured. Thanks for all your help in advance.
Tom
You can construct the query in the query editor.
As it's using a subquery (the SELECT... part) you'll need to type the query in the SQL mode (looks like notepad and you get there by selecting the [SQL] option from the top left button)
There you can check the query to work properly and after saving it e.g. with the name "qryMtnsuspUpdate" you can use in the above routine:
currentdb.execute ("qryMtnsuspUpdate")
That line will replace all recordset processing I've pasted in my previous comment.
A bit clearer ?
Nic;o)
As it's using a subquery (the SELECT... part) you'll need to type the query in the SQL mode (looks like notepad and you get there by selecting the [SQL] option from the top left button)
There you can check the query to work properly and after saving it e.g. with the name "qryMtnsuspUpdate" you can use in the above routine:
currentdb.execute ("qryMtnsuspUpdate")
That line will replace all recordset processing I've pasted in my previous comment.
A bit clearer ?
Nic;o)
UPDATE MTNSUSP INNER JOIN tblResponsible ON MTNSUSP.Code = tblResponsible.DisCode SET MTNSUSP.Responsible = tblResponsible.Responsible
WHERE tblResponsible.CusType = MTNSUSP.CusType;
then in your code create this field you need and call this query to execute. Will be much faster