1

I'm trying to update an MS Access database in Excel VBA using an ADO Recordset object. When I fire the rs.Update method, I would like the following code to wait until the database has been updated before executing.

It appears the database takes 3-5 seconds to register the update method. How can I delay the subsequent code from executing before this takes place?

Community
  • 1
  • 1
andy91
  • 157
  • 2
  • 14
  • I was definitely under the impression that `rs.Update` was a *synchronous* operation, and no delay should be necessary. What makes you think that it is not? – RBarryYoung Jul 13 '15 at 15:24
  • May be because my `PivotCache` which is pulling in data from the database utilises a separate connection to the `Recordset`. I create the `Connection` object on the fly whenever an `Update` is required, but the `PivotCache` is pointed at the DB when the worksheet is opened. – andy91 Jul 14 '15 at 08:58

2 Answers2

1

This is a simple function I now use to pause processing for a few seconds. First make sure you have the API call in your module:

Private Declare Sub sapiSleep Lib "kernel32" _
    Alias "Sleep" _
    (ByVal dwMilliseconds As Long)

Then add in this function:

Public Function sSleep(lngMilliSec As Long)
    If lngMilliSec > 0 Then
        Call sapiSleep(lngMilliSec)
    End If
End Function

So you can view how this works using: 

Public Function sTestSleep()
Const cTIME = 1000 'in MilliSeconds
    Call sSleep(cTIME)
    MsgBox "Before this Msgbox, I was asleep for " _
        & cTIME & " Milliseconds."
End Sub

So to pause code processing for 5 seconds you would say:

Call sSleep(5000)

All of this code was taken from here.

Newd
  • 2,174
  • 2
  • 17
  • 31
  • With that approach, how much CPU load do you see in the Window Task Manager while `Pause()` is doing its thing? – HansUp Jul 13 '15 at 14:47
  • @HansUp It definitely spikes up some, 40%ish (fairly slow computer) I generally only ever use it for a few seconds. Is there another method you are familiar with to "pause" processing. I am definitely not opposed to better options. I had just stolen this one from [here](http://stackoverflow.com/questions/6960434/timing-delays-in-vba) – Newd Jul 13 '15 at 14:58
  • 1
    In Access VBA, I prefer [the Windows API method](http://access.mvps.org/access/api/api0021.htm). Since this question is about Excel VBA, I would look into its [Application.Wait Method](https://msdn.microsoft.com/en-us/library/office/ff822851.aspx); I've never had occasion to use either in Excel, though. – HansUp Jul 13 '15 at 15:03
  • However now having read the alternatives in the aforementioned post, it seems like `Application.Wait(Now + TimeValue("0:00:01"))` would work in OP's situation since it seems like he is running this from Excel. – Newd Jul 13 '15 at 15:04
  • The link for the Windows API is definitely interesting. I will have to replace my current one with that in my system. Though your answer is better than mine so I would suggest posting it! – Newd Jul 13 '15 at 15:07
1

The Update method will actually block until the update is completed, therefore your code already "waits" for it. However, you must commit your transaction before others (users, transactions) can actually see the changes. When are you calling the CommitTrans method on the Connection object? Is this maybe causing your delay? If so, "pausing" for some seconds will not change anything.

Alex
  • 1,126
  • 1
  • 11
  • 24
  • The 'user' reading the new data is actually a `PivotCache` object connected to a Pivot Table. My code eventually runs a `PivotCache.Refresh` method. This does not utilise the same `Connection` object as is used in the `RecordSet.Update`. I imagine this is why I have a problem with the two not being 'in sync' – andy91 Jul 13 '15 at 15:11
  • 1
    Exactly, so you have to make sure your transaction is commited before the `PivotCache` is refreshing. Otherwise you can pause execution for ever and the data won't be up-to-date. Waiting to long to perform the commit can also lead to locks, so as soon as the state in the DB is valid again after your update you should immediately commit your transaction. – Alex Jul 13 '15 at 15:23