Do loop variable used in vb code

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)
Loop

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
Loop
busbeeadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
build an array to hold the SQL Strings

Dim strSQL() as String

Redim Preserve strSQL(i) as String

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

AW
0
Steve BinkCommented:
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.
0
busbeeadAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

busbeeadAuthor Commented:
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.
0
david251Commented:
busbeead,

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,

-David251



0
GugroCommented:
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.
0
Steve BinkCommented:
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
    etc...

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")
    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF
        strSQL = "UPDATE table SET " & rs!FieldName & " = Replace(" & rs!fieldname & ",'" & rs!SearchFor & "','" & rs!ReplaceWith & "')"
        db.Execute strSQL
        rs.MoveNext
    Loop
   
    rs.Close
    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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
david251Commented:
sorry arthurwood, I missed your previous post.

-David251
0
harfangCommented:
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)
    wks.BeginTrans
    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
    wks.CommitTrans
    Exit Sub
   
HeavyReplace_Error:
    MsgBox Err.Description, vbCritical, "Error " & Err.Number
    Err.Clear
    On Error Resume Next
    wks.Rollback
   
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
0
harfangCommented:
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...
Cheers:)
0
busbeeadAuthor Commented:
routinet,
    That is a great solution.  This will definately help out since I will not need to edit the code every time.  Thanks!
0
Steve BinkCommented:
Glad we could help you out.  :)  Good luck with the rest of your project!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.