none
Excel worksheet and update an Access database RRS feed

  • Question

  • I am trying to see if there is a change to when Excel worksheet and update an Access database.  I need some help with the program.  It keeps going to "Detect Insert" and gives me an error when it executes the code for the "Detect Insert".  Here is some of my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
                Dim conn As ADODB.Connection
                Dim rs As ADODB.Recordset
                Set conn = New ADODB.Connection
                Set rs = New ADODB.Recordset
                conn.Open                                     ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\file1.accdb;"
                rs.Open "SELECT * FROM [Table1];", conn, adOpenForwardOnly, adLockReadOnly, adCmdText
                rsarray = rs.GetRows()
                RecordCount = UBound(rsarray, 2) + 1
                Max = .Cells(Rows.Count, 2).End(xlUp).Row
                
    
                ' Detect UPDATE
                If RecordCount = Max - 4 Then 
    
    
                     'code
    
                 End If
    
                 'Detect INSERT
                If RecordCount < Max - 4 Then
    
                     'code
    
                 End If
    
                 'Detect DELETE
                If RecordCount > Max - 4 Then
    
                     'code
    
                 End If
    
    End Sub


    Wednesday, August 29, 2012 5:34 AM

All replies

  • I see one .cells in assignment to Max.Probably you did not post the with.

    Max = .Cells(Rows.Count, 2).End(xlUp).Row

    Try explicitly referreing

    Max =Me.Cells(Rows.Count, 2).End(xlUp).Row


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 29, 2012 11:48 AM
    Answerer