CODEWITHSUNDEEP
vbaexcelms-accessado
andy91
andy91

Reputation: 157

Recordset update delay

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?

Upvotes: 1

Views: 1290

Answers (2)

Newd
Newd

Reputation: 2185

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.

Upvotes: 1

Alex
Alex

Reputation: 1398

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.

Upvotes: 1

Related Questions