Timer event not firing, and unpredictable slowdowns in code.

I have an Access 2007 form timer event that (seemingly at random times)
fails to fire. I also have Access VBA code that slows down, sometimes
taking 4 or 5 minutes to do what it otherwise does in 5 seconds. As I
increase either the cpu speed or the number of cpus (e.g., from core to
quad), the problem gets much worse.

This is a problem both under 32-bit Vista and under 64-bit Windows Server
2008 RC0. It's been going on for a long time, and affects multiple
computers.

Any suggestions? I can't pinpoint any other software that could be causing
the problem. Right now, I'm left with the theory that it has something to
do with using SATA drives in a RAID configuration, so I'm testing changing
that. Are there some settings I should be changing to test this, or some
line of code to add?

Re: Timer event not firing, and unpredictable slowdowns in code.

The VBA timer component has very limited usage/reliability. You can
use the VBA timer for displaying an on/off gif asynchronously while
running a long sql query for example. But if you try to use the VBA
timer on a scheduled basis, you will run into problems because Access
does not support multithreading -- which is required (well, desired)
when using timer events on a scheduled basis. Your errors are being
caused by thread collisions within VBA -- trying to run 2 things on the
same thread - the timer and your routine.

The workaround would be to use something that supports multithreading
(.Net -- and not to scare you off of .Net -- but this will require OOP
since thread manipulation usually requires delegates).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Timer event not firing, and unpredictable slowdowns in code.

That makes perfect sense, unfortunately. I am not afraid--I've been
planning on switching everything to .net for sometime, but I didn't know the
form timer used by Access had a problem with multithreading. I assume that
thread collisions can explain both that the timer fails to fire sometimes
AND that sometimes my code slows down to nearly a standstill for no apparent
reason (looks like freezing, but it really is crawling, or at least half
sleeping). Would it also explain why the problem became much worse as the
speed and the number of processors increased?

Also, would the entire code have to be replaced, or in principle, as a
stopgap measure, couldn't I code the timer part in .net, and have it call
existing vba code as needed--presumably the .net code wouldn't conflict with
the vba code, since it wouldn't be on the same thread. Second, is it
possible that reducing the frequency with which the timer fires would help
(from six times per minute to once per minute)? I'm thinking that might
reduce the opportunities for bad collisions inside the thread.

This is easily the most useful post on a tech issue I've ever received,
given the difficulty I've had in trying to determine even the nature and
scope of the problem, let alone its cause, and the extent of the damage it's
causing.

If you happen to know of any good resources on the web for this, let me
know.

Thanks again--it was bad not to know whence sprang my troubles.


"Rich P" wrote in message
news:4738c3b7$0$10303$815e3792@news.qwest.net...
> The VBA timer component has very limited usage/reliability. You can
> use the VBA timer for displaying an on/off gif asynchronously while
> running a long sql query for example. But if you try to use the VBA
> timer on a scheduled basis, you will run into problems because Access
> does not support multithreading -- which is required (well, desired)
> when using timer events on a scheduled basis. Your errors are being
> caused by thread collisions within VBA -- trying to run 2 things on the
> same thread - the timer and your routine.
>
> The workaround would be to use something that supports multithreading
> (.Net -- and not to scare you off of .Net -- but this will require OOP
> since thread manipulation usually requires delegates).
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: Timer event not firing, and unpredictable slowdowns in code.

Note: Access is probably the most powerful micro RDBMS on the market
today (and probably has been since the beginning). But you can only
cram so much functionality into a Micro system -- in particular -- an
integrated database/development platform like Access.

The advantage of Access is that it can be used by non-programmers
through expert progragmmers, where .Net you need to already be very
proficient in OOP to take advantage of a lot of the features.

One nice thing with .Net to Access is that a dll can be created in .Net
which can be invoked in Access. This would be the workaround to your
issue.

Here is a snippet of code that uses delegates for a slideshow app I
wrote a few years ago in VB2005

-------------------------------------------------------
Public Delegate Sub StartSlideShowDelegate()
Private Delegate Sub ShowPicsDelegate(ByVal PicCount As Integer, ByVal
str2 As String)

Private Sub ShowPics(ByVal PicCount As Integer, ByVal str2 As String)
Try
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim omd As ShowPicsDelegate = New ShowPicsDelegate(AddressOf
ShowPics)
Dim arx As IAsyncResult = Me.BeginInvoke(omd, New Object()
{PicCount, str2})
Me.EndInvoke(arx)
Return
End If
If PicCount > 580 Then
Console.WriteLine(PicCount.ToString & ") " &
arrPics(PicCount).ToString)
End If
PicImage.Image = New Bitmap(arrPics(PicCount).ToString)
Me.Text = str2 & " " & arrPics(PicCount).ToString
lblPicCount.Text = (PicCount).ToString
Catch ex As Exception
Me.Text = str2 & " " & arrPics(PicCount).ToString & " Error: This
image not viewable"
lblPicCount.Text = (PicCount).ToString
End Try
End Sub
-------------------------------------------------------

A delegate is basically an Interface for Subs and Functions. And an
Interface is an abstract class that defines the structure of regular
classes, and this is significant when writing .Net DLLs for Com usage
(Access is a Com based program -- component object model -- old school
programming). The nice thing is that you can take advantage of the
functionality of .Net from within Access, although, I did have a
problem trying to use ADO.Net from within Access through a .Net DLL I
wrote. The problem ended up being some legal thing between Microsoft
and the world and for some reason they restricted ADO.Net from Access --
but not from Excel - my ADO.Net dll worked in Excel -- go figure)

VB.Net is basically a cross between C++ and VB (not sure if it is
recognized by the AKC yet). So you definitely need to be up on OOP to
take advantage of VB.Net. You probably don't need to be up on C++
programming to use VB.Net, but if you really want to get into the guts
of OOP, I would recommend a course in C++. And delegates is definitely
at the core of OOP.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Timer event not firing, and unpredictable slowdowns in code.

Thanks, that's helpful code. So far, I've managed to de-multi-task most of
my code and that solved much of the problem, but it still seems that it
appears in odd places. For example, one simple Access query--select the top
two records by descending primary key order where another field is equal to
a certain value--slows down *extremely* (we're talking one second jumping to
three minutes) as the cpu gets faster and number of cpus increase--is it
possible one basic access query can result in thread collisions?

(And I haven't figured out why one database is increasing in size
automatically and dramatically even when no editing is taking place--a
zombie database.)




"Rich P" wrote in message
news:4738e6a4$0$504$815e3792@news.qwest.net...
> Note: Access is probably the most powerful micro RDBMS on the market
> today (and probably has been since the beginning). But you can only
> cram so much functionality into a Micro system -- in particular -- an
> integrated database/development platform like Access.
>
> The advantage of Access is that it can be used by non-programmers
> through expert progragmmers, where .Net you need to already be very
> proficient in OOP to take advantage of a lot of the features.
>
> One nice thing with .Net to Access is that a dll can be created in .Net
> which can be invoked in Access. This would be the workaround to your
> issue.
>
> Here is a snippet of code that uses delegates for a slideshow app I
> wrote a few years ago in VB2005
>
> -------------------------------------------------------
> Public Delegate Sub StartSlideShowDelegate()
> Private Delegate Sub ShowPicsDelegate(ByVal PicCount As Integer, ByVal
> str2 As String)
>
> Private Sub ShowPics(ByVal PicCount As Integer, ByVal str2 As String)
> Try
> If Me.InvokeRequired Then
> ' if operating on a thread, invoke a delegate
> ' on the UI thread.
> Dim omd As ShowPicsDelegate = New ShowPicsDelegate(AddressOf
> ShowPics)
> Dim arx As IAsyncResult = Me.BeginInvoke(omd, New Object()
> {PicCount, str2})
> Me.EndInvoke(arx)
> Return
> End If
> If PicCount > 580 Then
> Console.WriteLine(PicCount.ToString & ") " &
> arrPics(PicCount).ToString)
> End If
> PicImage.Image = New Bitmap(arrPics(PicCount).ToString)
> Me.Text = str2 & " " & arrPics(PicCount).ToString
> lblPicCount.Text = (PicCount).ToString
> Catch ex As Exception
> Me.Text = str2 & " " & arrPics(PicCount).ToString & " Error: This
> image not viewable"
> lblPicCount.Text = (PicCount).ToString
> End Try
> End Sub
> -------------------------------------------------------
>
> A delegate is basically an Interface for Subs and Functions. And an
> Interface is an abstract class that defines the structure of regular
> classes, and this is significant when writing .Net DLLs for Com usage
> (Access is a Com based program -- component object model -- old school
> programming). The nice thing is that you can take advantage of the
> functionality of .Net from within Access, although, I did have a
> problem trying to use ADO.Net from within Access through a .Net DLL I
> wrote. The problem ended up being some legal thing between Microsoft
> and the world and for some reason they restricted ADO.Net from Access --
> but not from Excel - my ADO.Net dll worked in Excel -- go figure)
>
> VB.Net is basically a cross between C++ and VB (not sure if it is
> recognized by the AKC yet). So you definitely need to be up on OOP to
> take advantage of VB.Net. You probably don't need to be up on C++
> programming to use VB.Net, but if you really want to get into the guts
> of OOP, I would recommend a course in C++. And delegates is definitely
> at the core of OOP.
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***