find and update access record using excel vba

[RAYLWARD102]

I'm successfully writing values to an access DB table. At different points in my Excel VBA project, I may need to refer back to records stored in the access database for change from excel vba. I've been playing with the syntax, but can't seem to make a go of it. I'll post what I've got thus far.

I want to search the "TransactionSorting" table, column field "bins" for a value of 137. IF found, I would like to remove\clear the value.

Sub mrexcel()
    Set cont = Sheets("controls")
    strig = cont.Range("p15").Value & cont.Range("p16").Value 'file path and filename
    On Error Resume Next
    Set db = OpenDatabase(strig)
    Set rs = db.OpenRecordset("TransactionsSorting", dbOpenTable)
    i = "137" 'later to be supplied with variable
        With rs
            rs.MoveFirst
            While Not rs.EOF
                If rs.Fields("bins").Value = i Then
                    rs.Fields("bins").Value = ""
                    rs.Update
                    rs.MoveNext
                Else:
                    rs.MoveNext
                End If
            Wend
        End With

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

 

[Fazza]

Sounds like just an UPDATE query. No looping. Just a one off query. [Untested] SQL like

UPDATE TransactionsSorting
SET bins Is Null
WHERE bins = 137

No need to open the database either. Google should find examples.

 

[Fazza]

like

  Set objRS = CreateObject("ADODB.Recordset") 'late binding
  objRS.Open strSQL, strConnection
  Set objRS = Nothing

 

[RAYLWARD102]

I'll give that a try. I found that if I substituted the "" for a value like "15", my procedure would work and rest any numbers of 137 in the bins field. So istead of using "" I should use null?

 

[Fazza]

use null if you want a null, use "" if you want a zero length string. cheers