Do loop variable used in vb code

Posted on 2004-12-01
Last Modified: 2008-02-01
I have code that right now calls 89 different sql statements.  I would like to set up a simple do loop so that each time I add a line of code I only need to increment i in the do loop.  Below is junk code that I would like to get to work.  I am sure someone can help me out.  Thanks!

This code would define all the strings
Do Until i = 90 <- run 89 times and stop
Dim strSQLi As String  <- I would like this to increment so that I define 89 strSQL strings (strSQL1-89)

This code would execute the strings
Do Until i = 90 <- run 89 times and stop
Me.Text32 = "Code is running step i of 89"  <- I would like this to show the value of i in the textbox to echo where the code is to the form.
DoCmd.RunSQL strSQLi  <- I would like the i to increment here to equal the i in the do loop.  that way it would run each of the 89 sql commands
Question by:busbeead
    LVL 44

    Expert Comment

    build an array to hold the SQL Strings

    Dim strSQL() as String

    Redim Preserve strSQL(i) as String

    Do while i < 90
       Me.Text32 = "Code is running step " &  i  & " of 89"  
       DoCmd.RunSQL strSQL(i )

    LVL 50

    Expert Comment

    by:Steve Bink
    Do you need to keep and refer to the SQL for each of the 89 iterations?  Are the SQL statements at all similar?  Please post an example of some of the statements (perhaps 5 of them) so we can see if there is an easier way to do this.

    Author Comment

    I am basically doing a bunch of find and replaces.  My current code looks similar to this:

    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL89 As String

    strSQL1 = "UPDATE table SET [field] = Replace([field], chr(9), '')"
    strSQL2 = "UPDATE table SET [field1] = Replace([field1], "Door", "Window")"
    strSQL3 = "UPDATE table SET [field2] = Replace([field2], "Car", "Truck")"
    strSQL89 = "UPDATE table SET [field3] = Replace([field3], "Tennis", "Golf")"

    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.RunSQL strSQL89

    I want to get rid of the 89 lines of Dim... and 89 lines of DoCmd... to clean it up and make it easier to read and work with.

    Author Comment

    The code is very slow (over 30,000 items in the database), so I want to speed it up if possible.  I doubt this will do it, but at least it will be easier to read.
    LVL 19

    Expert Comment


    Why not use an array like this

    dim strSql(90) as string

    strSQL(1) = "UPDATE table SET [field] = Replace([field], chr(9), '')"
    strSQL(2) = "UPDATE table SET [field1] = Replace([field1], "Door", "Window")"
    strSQL(3) = "UPDATE table SET [field2] = Replace([field2], "Car", "Truck")"
    strSQL(89) = "UPDATE table SET [field3] = Replace([field3], "Tennis", "Golf")"

    for i =  1 to 90
    DoCmd.RunSQL strSQL(i)
    next i

    good luck,


    LVL 6

    Expert Comment

    If you want to speed up your updates why not use a single SQL statement:

    DoCmd.RunSQL "UPDATE table SET [field] = Replace([field], chr(9), '' ) , " & _
                                                       "[field1] = Replace([field1], 'Door', 'Window') ,  " & _
                                                       "[field2] = Replace([field2], 'Car', 'Truck'  ) ,  " & _
                                                        "[field3] = Replace([field3], 'Tennis', 'Golf' ) "

    it will update every row in the database only once and not 90 times.
    LVL 50

    Accepted Solution

    Another adaptation, though this is a little complex.

    1) Create another table with the following schema:
        ID (autonumber/PK)
        FieldName (text)
        SearchFor (text)
        ReplaceWith (text)

    2) Enter all your replacement data in the new table.  You can even set up a form to assist you with managing the list.
        ID  FieldName   SearchFor   ReplaceWith
        1   [Field]     Door        Window
        2   Field2      Car         Truck

    3) Alter your procedure to pull a recordset of everything from that table, then construct your UPDATE dynamically:
        Dim db as DAO.Database
        Dim rs as DAO.Recordset
        Dim strSQL as String

        Set db = CurrentDB
        Set rs = db.OpenRecordset("SELECT * FROM ReplacementTable ORDER BY ID")

        Do Until rs.EOF
            strSQL = "UPDATE table SET " & rs!FieldName & " = Replace(" & rs!fieldname & ",'" & rs!SearchFor & "','" & rs!ReplaceWith & "')"
            db.Execute strSQL
        Set rs = Nothing
        Set db = Nothing

    In the loop, you can add some error-checking code to make sure the update went through as planned, and with this method you do not ever need to edit the code.  To remove or add items for replacement, you just manage the new 'replacement' table.  Get it?
    LVL 19

    Expert Comment

    sorry arthurwood, I missed your previous post.

    LVL 58

    Expert Comment

    I like Gugro's solution, but it has two limitations.
    1) in real life, i'm sure the same field is updated several times, which will not work with just this syntax
    2) There are not enough line continuation characters for 89 lines :)

    I'd stick with David's solution, to which I would like to add a transaction. This will remove most of the disadvantages of running 89 different queries instead of just one. All writes will be "commited" only at the end, and only if no errors occured...

    Here is David's revised code:


    Sub HeavyReplace()

        Dim wks As DAO.Workspace
        Dim strSQL(1 To 89) As String
        Dim intQ As Integer

        strSQL(1) = "[field] = Replace([field], Chr(9), '')"
        strSQL(2) = "[field1] = Replace([field1], 'Door', 'Window')"
        strSQL(3) = "[field2] = Replace([field2], 'Car', 'Truck')"
        ' ...
        strSQL(89) = "[field3] = Replace([field3], 'Tennis', 'Golf')"
    On Error GoTo HeavyReplace_Error

        Set wks = Workspaces(0)
        With wks.Databases(0)
            For intQ = 1 To 89
                ' Me.txtProgressInfo = "Step " & intQ & " of 89"
                ' Me.txtProgressBar = String( intQ, "#" )
                ' Me.txtProgressDebug = strSQL(intQ)
                .Execute "UPDATE tblTableName SET " & strSQL(intQ)
            Next intQ
        End With
        Exit Sub
        MsgBox Err.Description, vbCritical, "Error " & Err.Number
        On Error Resume Next
    End Sub


     Use any or several of the Me.txtProgress* lines to see what's happening. I like the progress bar, everyone will understand instantly: it display a growing row of #############.

    Hope this helps
    LVL 58

    Expert Comment

    I was now going to suggest routinet's idea, not in so much detail, though :)
    Of course, this can also be wrapped up in a transaction to speed up the  queries. The final queries will be just the same, they will only be easier to maintain and update with the table approach...

    Author Comment

        That is a great solution.  This will definately help out since I will not need to edit the code every time.  Thanks!
    LVL 50

    Expert Comment

    by:Steve Bink
    Glad we could help you out.  :)  Good luck with the rest of your project!

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now