[AccessD] ADO Question

I don't remember ADO using bangs (!)!  Have you stepped through the code to see what's going on?  An ADO error needs its own handling because the ADO errors collection isn't the same object as the Access error collection.  That's probably why you aren't getting an error.  Here's an old example of ADO error handling.

Public Sub ExecuteADOCommand(cmdTemp As ADODB.Command, _
                          rstTemp As ADODB.Recordset)
    Dim errLoop As ADODB.Error
    Dim cnnErrs As ADODB.Errors
    
    Set cnnErrs = CurrentProject.Connection.Errors
    ' Run the specified Command object. Trap for
    ' errors, checking the Errors collection if necessary.
    On Error GoTo Err_Execute
    cmdTemp.Execute
    On Error GoTo 0

    ' Retrieve the current data by requerying the recordset.
    rstTemp.Requery
    Set errLoop = Nothing
    Set cnnErrs = Nothing
    Exit Sub

Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
    If cnnErrs.Count > 0 Then
        For Each errLoop In cnnErrs
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                errLoop.Description
        Next errLoop
        cnnErrs.Clear
    End If
    Resume Next
End Sub

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dale Kalsow
Sent: Wednesday, July 22, 2009 8: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