asked on

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

Arthur_Wood

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

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.

busbeead

ASKER

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.

busbeead

ASKER

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.

david251

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

Gugro

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.

ASKER CERTIFIED SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

david251

sorry arthurwood, I missed your previous post.

-David251

Markus Fischer

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

Markus Fischer

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

busbeead

ASKER

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

Steve Bink

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