[AccessD] ADO Question

Try just using adLockOptimistic.  You are using Batch Optimistic, and I believe that it is waiting for 'batch' commands.  I must admit I have never used batch processing with ADO...go figure.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dale Kalsow
Sent: Wednesday, July 22, 2009 10:18 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] ADO Question

Good Morning Everyone,
 
I am sure I am missing something simple here but can anyone tell me why me ADO code is not working.  I know the record set is being opened and the .count is working correctly, but all though the code is executed to write the record (and delete it) the table is not effected and an error is not being thrown.  
 
Thanks!
 
Dale
 
 
Dim conn As ADODB.Connection
    Dim rstRS As ADODB.Recordset
    Dim strSQL As String
        
    On Error GoTo EH_Form_AfterUpdate
    
    strSQL = "SELECT tblPlacedInServiceHead.CustomertoOwnerKey "
    strSQL = strSQL & "FROM tblPlacedInServiceHead "
    strSQL = strSQL & " WHERE (((tblPlacedInServiceHead.CustomertoOwnerKey)="
    strSQL = strSQL & Val(Me.txtCustomertoOwnerKey.Value)
    strSQL = strSQL & ") AND ((tblPlacedInServiceHead.PlacedInServiceDate) Is Null));"
    Rem Open the tblPlacedInServiceHead table
    Set conn = CurrentProject.Connection
    Set rstRS = New ADODB.Recordset
    With rstRS
        .Open strSQL, conn, adOpenKeyset, adLockBatchOptimistic
    End With
    
    If Me.cboPlaceInServiceReportRequired Then
        If rstRS.RecordCount > 0 Then
            Rem Record is found and there is nothing to do.
        Else
            Rem create a record
           rstRS.AddNew
            rstRS![CustomertoOwnerKey] = Val(Me.txtCustomertoOwnerKey.Value)
            rstRS.Update
        End If
    Else
        If rstRS.RecordCount = 0 Then
            Rem Record is not found and there is nothing to do.
        Else
            Rem delete a record
            rstRS.MoveFirst
            Do While Not rstRS.EOF
                rstRS.Delete
                rstRS.MoveNext
            Loop
        End If
    End If
    
    rstRS.Close
    conn.Close
    
    Set conn = Nothing
    Set rstRS = Nothing
    
    Exit Sub
    
EH_Form_AfterUpdate:
        MsgBox "Error " & Err.Number & ", " & Err.Description, _
              vbCritical, "UNABLE TO VERIFY VERSION"
    
End Sub


      
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.