Do loop variable used in vb code

Posted on 2004-12-01
Medium Priority
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
  • 3
  • 3
  • 2
  • +3
LVL 44

Expert Comment

ID: 12719844
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 51

Expert Comment

by:Steve Bink
ID: 12719850
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

ID: 12720084
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.
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.


Author Comment

ID: 12720170
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

ID: 12720561

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,



Expert Comment

ID: 12720763
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 51

Accepted Solution

Steve Bink earned 2000 total points
ID: 12720935
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

ID: 12721092
sorry arthurwood, I missed your previous post.

LVL 58

Expert Comment

ID: 12721120
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

ID: 12721157
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

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

Expert Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 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