Hi Rick, Alex and fellow students,

I recently went into a situation where the governement subdivided the country from 80 to over 250 counties. This meant that i had to update the backend of my database. Some of the users are very far and i cant practically reach them. So i wrote over 250+ queries in the frontend so it can update the backend with the new counties. Problem was that when these queries were running, Access would hang for a while creating a bad user experience.

This is what i did to solve the problem. I hope it helps someone and maybe someone can even improve it.

First, i write the SQL strings, join them into one string, create an array of these strings and then loop through each one of them inserting the data into the table. With a DoEvents in the loop, the database does not hang. The loop also allows me to attach a custom progress bar so the user can know whats going on rather than hanging.

'my SQL strings are below. this is just an example. the SQL statements were much longer.
'notice that since the first part of the query is constant [DoCmd.RunSQL "INSERT INTO CountyT (County,StateID) VALUES "], i chose to have it on the loop instead of repeating it 250+ times.

MySQL1 = "('Jinja', 1);"
    MySQL2 = "('Iganga', 2);"
    MySQL3 = "('Luuka', 3);"
    MySQL4 = "('Kampala', 4);"
    MySQL5 = "('Juba', 5);"
    MySQL6 = "('Maridi', 6);"
    MySQL7 = "('Maban', 7);"
    MySQL8 = "('Mawundo', 8);"
    MySQL9 = "('Waibuga', 9);"
    MySQL10 = "('Malakal', 10);"

'at this point, i join the above variables into one long string. Notice below that i have a double period between the variables. i will use this as a criteria to split this string into an array. i couldnt use a comma because the above variables have comas in them. one period wouldnt work in this case either because some of the data i had to insert into the backend had email addresses [which always have a period like .com]

Dim SQL as string
SQL = "" & MySQL1 & ".." & MySQL2 & ".." & MySQL3 & ".." & MySQL4 & ".." & MySQL5 & ".." & MySQL6 & ".." & MySQL7 & ".." & MySQL8 & ".." & MySQL9 & ".." & MySQL10 & ""

  
'now i create an array from the above string
Dim MySQL() As String
MySQL = Split(SQL, "..")


'i start up a custom progress bar here. not shown as outside the scope of this example
    
    'then loop through my SQL statements one by one
DoCmd.SetWarnings False
    For i = LBound(MySQL) To UBound(MySQL)
        DoCmd.RunSQL "INSERT INTO CountyT (County,StateID) VALUES " & Trim(MySQL(i))        
        DoEvents      
        
    Next i  

DoCmd.SetWarnings true


So the whole code looks like below;


Private Sub Form_Close() 'On close of the start up form

X = Nz(DCount("CountyID", "CountyT"), 0)
    If X < 90 Then
Application.SetOption "Show Status Bar", False 'hide Statusbar momentarily since i have a custom progress bar
UpdateCountyT 'i had 80 counties initially. this IF ensures that the code runs only once.
Application.SetOption "Show Status Bar", True
End Sub


Private sub UpdateCountyT

MySQL1 = "('Jinja', 1);"
    MySQL2 = "('Iganga', 2);"
    MySQL3 = "('Luuka', 3);"
    MySQL4 = "('Kampala', 4);"
    MySQL5 = "('Juba', 5);"
    MySQL6 = "('Maridi', 6);"
    MySQL7 = "('Maban', 7);"
    MySQL8 = "('Mawundo', 8);"
    MySQL9 = "('Waibuga', 9);"
    MySQL10 = "('Malakal', 10);"

Dim SQL as string
SQL = "" & MySQL1 & ".." & MySQL2 & ".." & MySQL3 & ".." & MySQL4 & ".." & MySQL5 & ".." & MySQL6 & ".." & MySQL7 & ".." & MySQL8 & ".." & MySQL9 & ".." & MySQL10 & ""

  
'create a string array from the above string
Dim MySQL() As String
MySQL = Split(SQL, "..")


'start up a custom progress bar here
    
    'then loop through my SQL statements one by one
DoCmd.SetWarnings False
    For i = LBound(MySQL) To UBound(MySQL)
        DoCmd.RunSQL "INSERT INTO CountyT (County,StateID) VALUES " & Trim(MySQL(i))        
        DoEvents      
        
    Next i  

DoCmd.SetWarnings true


End Sub

---

Reply from Alex Hedley:

Thanks for sharing.