Solved

Need to speed up my code.

Posted on 2004-04-21
6
194 Views
Last Modified: 2011-10-03
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("Responsible", dbText, 20)
    td.Fields.Append f
   
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Mtnsusp")
    Set rst1 = dbs.OpenRecordset("tblResponsible")
   
   
    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
0
Comment
Question by:tkrpata
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:alexgud
ID: 10882513
Create update query in sql view like this:

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
0
 
LVL 5

Expert Comment

by:waelothman
ID: 10882574
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
0
 
LVL 5

Expert Comment

by:waelothman
ID: 10882591
sorry i was a\writing my comment same time with alexgud i didn't see his comment
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10882594
The recordset processing loop is slowing down your processing.
Using an UPDATE query will fasten the process significantly.
This query would look like:
"UPDATE Mtnsusp  SET Responsible = (SELECT Responsible FROM tblResponsible WHERE tblResponsible.DisCode = Mtnsusp.MtnCode AND tblResponsible.CusType = Mtnsusp.MtnCusType)"

and when saved in the query section e.g. named "qryMtnsuspUpdate"
you can execute it like:

currentdb.execute ("qryMtnsuspUpdate")

This will replace:
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Mtnsusp")
    Set rst1 = dbs.OpenRecordset("tblResponsible")
   
   
    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

Clear ?

Nic;o)
0
 
LVL 1

Author Comment

by:tkrpata
ID: 10888293
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10889690
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)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question