How do I know when database updated?

[moonbase]

I have a form F1 which adds a record to a table T1 using ADO. On completion form F1 opens a second form F2 which is bound to the table T1. The problem is that the new record isn't visible in F2.

The record is created correctly and the recordset and connection are closed. If I step through with debug the record IS there so it looks to be a timimg issue.

I can get round the problem by looping with a dlookup until the record appears (>100 iterations) but is there a better/proper way of ensuring the record is written?

 

[BNPMike]

You could try wrapping it in a transaction (begin...commit). That might flush the cache better.

 

[FancyPrairie]

You might try issuing the DoEvents command after you add the record. That will force Access to finish what it's doing prior to going on.

 

[cmmrfrds]

Try doing a resync on the recordset before leaving form 1.

 

[ineuw]

Which ever method you're using, macros or VBA, you need to requery the data table to see the record in form 2. That is what all the other replies are telling you in a roundabout way.

 

[moonbase]

Thanks for the advice but I am still having problems with this. The recordset and connection are closed before the form is opened and committing/requerying/resynching on the recordset has no effect. Requerying the form does work but I have to loop many times before the record appears.

I thought it may be something to do with calling one form from another but now I call both from an outer form and this has no effect.

I have now got another example in the same application which is more straightforward. In a form, I open a connection and recordset, read through the recordset and populate one field, close the recordset and connection. I then open a report which prints from the recordset but the field I have populated is not there (blank). I have set up a loop so that I can return to print again and on the second time it is there. So again it is a timimg issue.

This seems like a fundamental issue to me because surely applications rely on the database being updated when a recordset is closed and it throws into doubt my whole use of Access. I have done some searching and I wonder if it someting to do with asynchronous connections or mixing ADO with DAO. Do I need to use the committranscomplete event? I am getting out of my depth and this is not covered by Getz and Gilbert.

How come other people aren't hit by this problem all the time?

 

[hymn]

You need to save the record before printing if you have'nt saved it it is not in the underlying table/query

Hope this helps
Hymn

 

[moonbase]

We are beyond this point. The recordset is correctly updated it's just that there doesn't seem to be a guaratee when it will be available to a subsequent Access process. In the example above, when I run the report for a second time it is there, if a step through it is there, if I pause execution it is there. However if I let the process run through, it isn't there. The only way I can get round it is by looping on a DLookUp until it appears.

 

[Hap007]

Dear moonbase,

The possible reason other people are not seeing the same problem is more than likely because you are coding your program in a slightly different matter. This is not a bad thing, but it appears to be causing you problems. Since you see the same issue in multiple areas of your program, I would guess that you used the same code logic, so the same problem persists.

Here is a suggestion for you to try.
You probably define your ADO fields on entry to each function or procedure.
Instead, why not create a code module in the 'Modules' section. Now define all the ADO types that you use as Global. Now use the same variables in each function or subsoutine. Use the same record set variable to update as you use to read the data. Makesure to close the recordset before exit of each routine. Doing this will insure that updates are applied before the next group of records are retrieved in a different routine.

This is just a Thought to try.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[cmmrfrds]

If you provide the code you are using to update the recordset that will help in solving the problem otherwise there is a lot of guessing/assumptions on how you have things setup.

 

[moonbase]

Here is the code as it stands. First I update the recordset with the cheque no. The report prints cheques including the cheque no. If the highlighted code is omitted the cheque no. doesn't print on the first pass but if you select to reprint it does. (If anybody is interested but the code is long winded I will try to abridge.)


Option Compare Database
Option Explicit

' store cheque nos in transactions

Private Sub UpdateTrans(intPass As Integer)

Dim rstTran As Recordset
Dim lngChequeNo As Long

On Error GoTo Err_Routine

' get cheque fee transactions
ConnectProject
Set rstTran = New Recordset
strSQL = "SELECT * " _
& "FROM tblTransaction " _
& "WHERE blnTrnChequeRequired = True "
rstTran.Open strSQL, cnnProject, adOpenKeyset, adLockPessimistic, adCmdText
On Error Resume Next
rstTran.MoveFirst
On Error GoTo Err_Routine

' initial
lngChequeNo = CLng(Me.txtFirstChequeNo)

' each transaction
Do While Not rstTran.EOF
If intPass = 1 Then
rstTran!strTrnOurCheque = Format(lngChequeNo, "000000")
Else
rstTran!blnTrnChequeRequired = False
rstTran!blnTrnChequePrinted = True
rstTran!dtmTrnChequeDate = Date
rstTran!strTrnSystemUser = CurrentUser()
rstTran!dtmTrnSystemDate = Now()
End If
rstTran.Update

' next
rstTran.MoveNext
If lngChequeNo = 999999 Then
lngChequeNo = 1
Else
lngChequeNo = lngChequeNo + 1
End If
Loop

Exit_Routine:
On Error Resume Next

' close recordset
rstTran.Close
Set rstTran = Nothing
DisconnectProject

Exit Sub

Err_Routine:
MsgBox Err.Description
Resume Exit_Routine

End Sub

' cancel

Private Sub cmdCancel_Click()

On Error GoTo Err_cmdCancel_Click

' close form
DoCmd.Close acForm, Me.Name

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

' print cheques

Private Sub cmdPrint_Click()

On Error GoTo Err_cmdPrint_Click

If DCount("lngTrnID", "tblTransaction", "blnTrnChequeRequired = True") = 0 Then
MsgBox "No cheques to be printed.", vbExclamation, "Print Cheques"
cmdCancel_Click
Exit Sub
End If

' validate cheque no
If Not IsNumeric(Nz(Me.txtFirstChequeNo)) Then
MsgBox "Invalid first cheque number.", vbExclamation, "Print Cheques"
Me.txtFirstChequeNo.SetFocus
Exit Sub
End If
If Nz(Me.txtFirstChequeNo) < 1 Then
MsgBox "Invalid first cheque number.", vbExclamation, "Print Cheques"
Me.txtFirstChequeNo.SetFocus
Exit Sub
End If

' update trans with cheque no
' and confirm updated
UpdateTrans 1
[highlight] Do While IsNull(DLookup("lngTrnID", "tblTransaction", "strTrnOurCheque = '" & Format(CLng(Me.txtFirstChequeNo), "000000") & "'"))
DoEvents
Loop[/highlight]

' print cheques
'MsgBox "Load cheque stationery.", vbOKOnly + vbInformation, "Print Cheques"
Do
DoCmd.OpenReport "rptChequePrint", acViewNormal
DoEvents

' confirm
Select Case MsgBox("Have cheques printed correctly? Yes to update files. No to reprint. Cancel to abandon", vbYesNoCancel + vbDefaultButton3 + vbQuestion, "Print Cheques")
Case vbCancel
Exit Sub
Case vbYes
Exit Do
End Select
Loop

' update trans
UpdateTrans 2

' close form
DoCmd.Close acForm, Me.Name

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

 

[cmmrfrds]

How many records are updated in the routine and subsequently reported on? 1000? 10,000?

The 2 things that jump out to me are "adOpenKeyset, adLockPessimistic"

What are the requirement for these options? Is the SQL on linked tables? If so, what is the server?

 

[moonbase]

Haha. There is just 1 record. Tables aren't linked. Database is local Access mdb. Options are just defaults.

But I think you have the answer somewhere ...

If I had said 1,000,000 records you would have said that was the explanation but that's the point I am trying to understand. For Access to be at all reliable you have to know for certain when the database is updated, whether it's 1 or 1,000,000 records.

To my simple mind the database should be updated after closing the recordset and before the next operation. If there was 1,000,000 records, there might be a long wait, but at least you would know what was happening.

What am I missing that enables thousands of Access applications to work successfully?

 

[cmmrfrds]

Since the number of records is small, which is the usual situation for recordsets, there is no need for a keyset cursor. The keyset cursor may be useful on large recordset or lots of concurrent users. The first thing I would try is to make it a static cursor which provides the most functionality. See what happens with the static cursor that is client side.
rstTran.CursorLocation = adUseClient
rstTran.Open strSQL, cnnProject, adOpenStatic, adLockPessimistic, adCmdText