By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Multi Threading

100+
P: 157
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo error_handling
  3. Call Fremdriftsindikator("ja", 11, "Åpner vareinformasjon")
  4. Dim VisBilde As Boolean
  5. Dim Varenummer As Long
  6.  
  7. Dim rst As New ADODB.Recordset
  8. With rst
  9.     .Open "SELECT * from [Main tbl brukere] where brukernavn='" & Brukernavn() & "'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  10.     Varenummer = ![Siste vareinfo brukt]
  11.     Me.cmbbox_maskinlokasjoner = ![Vareinfo MaskinLokasjoner]
  12.     Me.chkbox_tablettbilde = ![VisVareinfoBilde]
  13.     Me.chkbox_nagarainformasjon = !VisNagaraInfo
  14.     .Close
  15. End With
  16. Set rst = Nothing
  17. Dim sSql As String
  18.  
  19. sSql = "SELECT [VARER Vareinformasjon].* FROM [VARER Vareinformasjon] WHERE ((([VARER Vareinformasjon].Varenr)=" & Varenummer & "));"
  20. Me.RecordSource = sSql
  21.  
  22. Me.Caption = "Vareinformasjon " & Me.tbx_varenr
  23.  
  24.  
  25.  
  26.  
  27.  
  28.  
  29. Call Fremdriftsindikator(, , "Henter opp tablettbilde", , 1)
  30. Call Oppdater_Tablettbilde
  31.  
  32. 'Fjernet dagens prod grunnet uvisst om den har noe for seg å vise
  33. Call Fremdriftsindikator(, , "Beregner produksjonsdata", , 1)
  34. Call oppdater_produksjonsdata
  35.  
  36. Call Fremdriftsindikator(, , "Henter opp logidosedata", , 1)
  37. Call liste_logidosedata
  38.  
  39. Call Fremdriftsindikator(, , "Henter informasjon fra Astra", , 1)
  40. Call Astradata
  41.  
  42. Call Fremdriftsindikator(, , "Henter opp lokasjonsdata", , 1)
  43. Call oppdater_lokasjonsdata
  44.  
  45. Call Fremdriftsindikator(, , "Beregner lokasjonssliste", , 1)
  46. Call oppdater_lokasjonsinfo
  47.  
  48. Call Fremdriftsindikator(, , "Henter opp nagaravareinformasjon", , 1)
  49. Call oppdater_nagarainformasjon
  50.  
  51.  
  52. Call Fremdriftsindikator(, , "Sjekker varemottak for bestilling", , 1)
  53. Call ventes_varemottak
  54.  
  55. Call Fremdriftsindikator(, , , "ja")
  56. [Form_INFO Vareinformasjon].tbx_søkestreng.SetFocus
  57.  
  58. Exit Sub
  59.  
  60. error_handling:
  61. MsgBox Err.Description
  62. Resume Next
  63.  
  64.  
  65.  
  66. End Sub
The 'fremdriftsindikator' 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_logidosedata' can be initiated before 'Call oppdater_produksjonsdata' is finished.
Maybe even all the function can be launched simultaneously. That would be cool :=)

Anybody have an idea?
Feb 28 '10 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,597
To the best of my knowledge, you cannot 'Multithread' in 'VBA', namely running two or more independent processes concurrently.
Feb 28 '10 #2

missinglinq
Expert 2.5K+
P: 3,532
That's correct. If you want Visual Basic behavior you'll have to use Visual Basic, not Access VBA! Sorry!

Linq ;0)>
Feb 28 '10 #3

100+
P: 157
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/techno...q/default.aspx

-

Still sure it cannot be done?
Mar 1 '10 #4

ADezii
Expert 5K+
P: 8,597
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.
Mar 1 '10 #5

100+
P: 157
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 :=)
Mar 1 '10 #6

Expert Mod 2.5K+
P: 2,545
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
Mar 1 '10 #7

100+
P: 157
Been there done that :)

All my request either use DAO with begintrans and committrans or ADO with AdOpenForwardOnly and AdLockReadOnly (Firehose), and all search fields are indexed.
Mar 2 '10 #8

ADezii
Expert 5K+
P: 8,597
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.
Mar 2 '10 #9

100+
P: 157
Exciting. This could open up a new dimension :=)
Mar 2 '10 #10

ADezii
Expert 5K+
P: 8,597
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Declare Function CreateThread Lib "kernel32" (ByVal lpThreadAttributes As Any, _
    2.                         ByVal dwStackSize As Long, ByVal lpStartAddress As Long, lpParameter As Any, _
    3.                         ByVal dwCreationFlags As Long, lpThreadID As Long) As Long
    4.  
    5. Public Declare Function TerminateThread Lib "kernel32" (ByVal hThread As Long, ByVal dwExitCode As Long) As Long
    6. Declare Function GetCurrentThread Lib "kernel32" () As Long
    7. Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    8. Declare Function GetExitCodeThread Lib "kernel32" (ByVal hThread As Long, lpExitCode As Long) As Long
    9. Private Declare Sub ExitThread Lib "kernel32" (ByVal dwExitCode As Long)
  2. Calling Procedure that Creates the actual Thread:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub Test_Sub()
    2. On Error Resume Next
    3. Dim hThread As Long
    4.  
    5. 'Create a Thread and provide the Address for the Function (AsyncThread()) to the
    6. 'API using the AddressOf Operator. It is my understanding that hThread is the Handle
    7. 'to the Thread amd is typically used to Terminate the Thread, but apparently not
    8. 'in this case, since it is Terminated in the AsyncThread() Procedure.
    9. hThread = CreateThread(ByVal 0&, ByVal 0&, AddressOf AsyncThread, 0&, ByVal 0&, 0&)
    10.  
    11. 'Closes the Handle to the Thread once it creates it. I tested it and it does return
    12. 'a Non-Zero Value which indicates that it succeeded
    13. CloseHandle hThread
    14. End Sub
  3. Function Procedure for which the Thread was created:
    Expand|Select|Wrap|Line Numbers
    1. Public Function AsyncThread()
    2. On Error Resume Next
    3. Dim lpExitCode As Long
    4. Dim lngCounter As Long
    5. Dim dblResult As Double
    6.  
    7. For lngCounter = 1 To 100000000
    8.   dblResult = lngCounter / 2
    9. Next
    10.  
    11. MsgBox "Done in AsyncThread"
    12.  
    13. 'Clean up Thread
    14. AsyncThread = True
    15.  
    16. 'Retrieves the Termination Status of the Thread, if Non-Zero
    17. 'then indicates success
    18. GetExitCodeThread GetCurrentThread, lpExitCode
    19.  
    20. ExitThread lpExitCode
    21.  
    22. 'Supposed to Terminate a Thread. I have no idea what the difference is
    23. 'between ExitThread and TerminateThread
    24. TerminateThread GetCurrentThread, lpExitCode
    25. End Function
Mar 2 '10 #11

100+
P: 157
Hahaha... lol

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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim lpExitCode As Long
  3. Dim lngCounter As Long
  4. Dim dblResult As Double
  5.  
  6. For lngCounter = 1 To 10000000
  7.   dblResult = lngCounter / 2
  8. Next
  9.  
  10. MsgBox "Done in AsyncThread" &
  11.  
  12. 'Clean up Thread
  13. AsyncThread = True
  14.  
  15. 'Retrieves the Termination Status of the Thread, if Non-Zero
  16. 'then indicates success
  17. 'GetExitCodeThread GetCurrentThread, lpExitCode
  18.  
  19. 'ExitThread lpExitCode
  20.  
  21. 'Supposed to Terminate a Thread. I have no idea what the difference is
  22. 'between ExitThread and TerminateThread
  23. 'TerminateThread GetCurrentThread, lpExitCode
Now it dont crash. But i really dont know

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


Edit:

msgbox CreateThread(ByVal 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
Mar 2 '10 #12

ADezii
Expert 5K+
P: 8,597
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.
Mar 2 '10 #13

100+
P: 157
Hmm.. if somebody else have any good ide we sure could use some now :=)
Mar 2 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.