Multi Threading

[MrDeej]

Hello!

I have a form in my database which runs trough 6 different functions at the Form_Open event to fill the form with fresh data.

This takes some time, maybe 2 or 3 seconds to open it. Yesterday we hade to use this form to lookup data on maybe 500 different products and I got a little bit annoyed of the delay. Occasionally it takes upto 20-30 seconds also, but that is not the case.

The code looks like this

Private Sub Form_Open(Cancel As Integer)
On Error GoTo error_handling
Call Fremdriftsindikator("ja", 11, "Åpner vareinformasjon")
Dim VisBilde As Boolean
Dim Varenummer As Long

Dim rst As New ADODB.Recordset
With rst
    .Open "SELECT * from [Main tbl brukere] where brukernavn='" & Brukernavn() & "'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    Varenummer = ![Siste vareinfo brukt]
    Me.cmbbox_maskinlokasjoner = ![Vareinfo MaskinLokasjoner]
    Me.chkbox_tablettbilde = ![VisVareinfoBilde]
    Me.chkbox_nagarainformasjon = !VisNagaraInfo
    .Close
End With
Set rst = Nothing
Dim sSql As String

sSql = "SELECT [VARER Vareinformasjon].* FROM [VARER Vareinformasjon] WHERE ((([VARER Vareinformasjon].Varenr)=" & Varenummer & "));"
Me.RecordSource = sSql

Me.Caption = "Vareinformasjon " & Me.tbx_varenr






Call Fremdriftsindikator(, , "Henter opp tablettbilde", , 1)
Call Oppdater_Tablettbilde

'Fjernet dagens prod grunnet uvisst om den har noe for seg å vise
Call Fremdriftsindikator(, , "Beregner produksjonsdata", , 1)
Call oppdater_produksjonsdata

Call Fremdriftsindikator(, , "Henter opp logidosedata", , 1)
Call liste_logidosedata

Call Fremdriftsindikator(, , "Henter informasjon fra Astra", , 1)
Call Astradata

Call Fremdriftsindikator(, , "Henter opp lokasjonsdata", , 1)
Call oppdater_lokasjonsdata

Call Fremdriftsindikator(, , "Beregner lokasjonssliste", , 1)
Call oppdater_lokasjonsinfo

Call Fremdriftsindikator(, , "Henter opp nagaravareinformasjon", , 1)
Call oppdater_nagarainformasjon


Call Fremdriftsindikator(, , "Sjekker varemottak for bestilling", , 1)
Call ventes_varemottak

Call Fremdriftsindikator(, , , "ja")
[Form_INFO Vareinformasjon].tbx_søkestreng.SetFocus

Exit Sub

error_handling:
MsgBox Err.Description
Resume Next



End Sub

The 'fremdriftsindi kator' function updates a progressbar which is a separate form. All the other functions is the ones that take time. They have code in them which opens tables on a SQL server and put the data on the form.


Recently one of our guys which handle VB applications talked to me about multithreading. I am not a proffesionale programmer so i dont know if it can be done, but i can see the advantage if 'Call liste_logidosed ata' can be initiated before 'Call oppdater_produk sjonsdata' is finished.
Maybe even all the function can be launched simultaneously. That would be cool :=)

Anybody have an idea?

[ADezii]

To the best of my knowledge, you cannot 'Multithread' in 'VBA', namely running two or more independent processes concurrently.

[missinglinq]

That's correct. If you want Visual Basic behavior you'll have to use Visual Basic, not Access VBA! Sorry!

Linq ;0)>

[MrDeej]

Here is someone who have managed this in Excel VBA. I tried to use his code but i think there is something missing.

http://www.mrexcel.com/forum/showthread.php?t=237639

And some more things:
"Every VBA 6.3-enabled product includes support for modeless dialog boxes, full core language parity with Visual Basic 6.0, enhanced project password protection, plus support for developer add-ins within the IDE that make programmers more productive. Some products that host VBA 6.3 may also take advantage of digital signing of VBA projects (to protect against "macro viruses"), support for ActiveX Control designers (easing the creation of complex project components), and multithreaded projects (for better performance with server-based applications)."

http://msdn.microsoft.com/isv/technology/vba/faq/default.aspx

-

Still sure it cannot be done?

[ADezii]

I'll look into it in greater depth later, but it appears that you are attempting to accomplish something via the API that the Programming Language (VBA) is not intrinsically capable of.

[MrDeej]

Thanks! I have searched alot on google for this, but i am stuck. I have found different functions like CreateThread wich seems that can be used, i have gotten a lot closer but there is always something that seems missing from the code. Maybe a reference or something

Hope you can understand this cause i think we would have great use of this in different functions if it works like i think now :=)

[Stewart Ross]

Hi. I think you may gain more advantage from optimising the performance of the existing functions run at open than through multi-threading. If the startup tasks involve running SQL statements or opening recordsets you will gain far more by ensuring that the joins are on indexed fields, that the use of IIF statements and domain aggregate functions is minimised, and so on. Otherwise all you will be doing is running a number of inefficient statements semi-concurrently, having spent a lot of time that may have been better spent on query optimisation in investigating multi-threading instead.

-Stewart

[MrDeej]

Been there done that :)

All my request either use DAO with begintrans and committrans or ADO with AdOpenForwardOn ly and AdLockReadOnly (Firehose), and all search fields are indexed.

[ADezii]

MrDeej, I do believe that I have the MultiThread Code actually working, via atempting to execute a Function within its own process space (Thread). I'll get back to you on this later.

[MrDeej]

Exciting. This could open up a new dimension :=)

[ADezii]

I have had some 'limited' success with the code, but unfortunately, simply do not have the time to dig deeper into this concept and related code. I am handing it over to you along with the appropriate Declarations, self-contained code segments, and hopefully helpful comments. Please be advised that I accept no responsibility whatsoever as far as the posted code since it WILL CRASH ACCESS AND LEAVE IT IN AN INCONSISTENT STATE! I do not think that I have to tell you this, but do not experiment with this or similar code on your Live/Production Database. If you do, at some point, get it operational please let us know since we all would be interested. Good luck.

1. Required Declarations:
   

   Public Declare Function CreateThread Lib "kernel32" (ByVal lpThreadAttributes As Any, _
                           ByVal dwStackSize As Long, ByVal lpStartAddress As Long, lpParameter As Any, _
                           ByVal dwCreationFlags As Long, lpThreadID As Long) As Long
                           
   Public Declare Function TerminateThread Lib "kernel32" (ByVal hThread As Long, ByVal dwExitCode As Long) As Long
   Declare Function GetCurrentThread Lib "kernel32" () As Long
   Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
   Declare Function GetExitCodeThread Lib "kernel32" (ByVal hThread As Long, lpExitCode As Long) As Long
   Private Declare Sub ExitThread Lib "kernel32" (ByVal dwExitCode As Long)

2. Calling Procedure that Creates the actual Thread:
   

   Public Sub Test_Sub()
   On Error Resume Next
   Dim hThread As Long
   
   'Create a Thread and provide the Address for the Function (AsyncThread()) to the
   'API using the AddressOf Operator. It is my understanding that hThread is the Handle
   'to the Thread amd is typically used to Terminate the Thread, but apparently not
   'in this case, since it is Terminated in the AsyncThread() Procedure.
   hThread = CreateThread(ByVal 0&, ByVal 0&, AddressOf AsyncThread, 0&, ByVal 0&, 0&)
   
   'Closes the Handle to the Thread once it creates it. I tested it and it does return
   'a Non-Zero Value which indicates that it succeeded
   CloseHandle hThread
   End Sub

3. Function Procedure for which the Thread was created:
   

   Public Function AsyncThread()
   On Error Resume Next
   Dim lpExitCode As Long
   Dim lngCounter As Long
   Dim dblResult As Double
   
   For lngCounter = 1 To 100000000
     dblResult = lngCounter / 2
   Next
   
   MsgBox "Done in AsyncThread"
   
   'Clean up Thread
   AsyncThread = True
   
   'Retrieves the Termination Status of the Thread, if Non-Zero
   'then indicates success
   GetExitCodeThread GetCurrentThread, lpExitCode
   
   ExitThread lpExitCode
   
   'Supposed to Terminate a Thread. I have no idea what the difference is
   'between ExitThread and TerminateThread
   TerminateThread GetCurrentThread, lpExitCode
   End Function

[MrDeej]

Hahaha... lol

I got a messagebox from M$ which says they are sorry that Access is crashing so often.

On Error Resume Next
Dim lpExitCode As Long
Dim lngCounter As Long
Dim dblResult As Double
  
For lngCounter = 1 To 10000000
  dblResult = lngCounter / 2
Next
  
MsgBox "Done in AsyncThread" &
  
'Clean up Thread
AsyncThread = True
  
'Retrieves the Termination Status of the Thread, if Non-Zero
'then indicates success
'GetExitCodeThread GetCurrentThread, lpExitCode
  
'ExitThread lpExitCode
  
'Supposed to Terminate a Thread. I have no idea what the difference is
'between ExitThread and TerminateThread
'TerminateThread GetCurrentThread, lpExitCode

Now it dont crash. But i really dont know

Msgbox GetCurrentthrea d gives -2 regardless of have many threads i have open.


Edit:

msgbox CreateThread(By Val 0&, ByVal 0&, AddressOf AsyncThread, 0&, ByVal 0&, 0&) gives me a value of 950 -> , this i suspect is the ThreadID. How can we get that into the asynchrous function... hmmm

[ADezii]

You can get the Thread ID into the Asynchronous Function by either:

1. Passing it as an Argument to the Function from which it is Referenced.
2. Declare the Thread ID as a 'Public' Variable where it can easily be Referenced.

P.S. - I don't think that you will be very successful with the first approach, but not really sure.

[MrDeej]

Hmm.. if somebody else have any good ide we sure could use some now :=)