Updating MS Access record using Excel vba with record Id
<p>I am trying to update 4 cells in Excel 2010 to my MS Access 2010 using VBA the following code. The problem is that sometimes it update those fields in the table record and other times it is not. I wonder if anyone can shed some light on my code on what is causing to be inconsistent.</p>

<hr>

<pre><code> 'Add a reference to the Microsoft ActiveX Data 2.8 or later Object Library
 'via the Tool | References... in the VB-editor
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String
Dim stCon As String

 'Instantiate the ADO COM's objects.
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

 'Pathway and name of the database

 stDB = "P:\Quote Log.mdb"
 'Create the connectionstring.
stCon = "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" &amp; _
"Data Source=" &amp; stDB &amp; ";"

 'Open the connection
cnt.Open stCon

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Find and Update the record in Access
With rst
    .Index = "PrimaryKey"
    .CursorLocation = adUseServer
    .Open "tbQuote", cnt, 1, 3, adCmdTableDirect
    .Seek Range("dx32").Value
    If Not rst.EOF Then
    .Fields("QuoteNum") = Sheets("Quote Notice").Range("dx32").Value

        .Fields("OtherCost") = Sheets("Cost").Range("b3").Value
        .Fields("StocklistCost") = Sheets("Cost").Range("b4").Value
        .Fields("DesignHrs") = Sheets("Cost").Range("b5").Value
        .Fields("ProductionHrs") = Sheets("Cost").Range("b6").Value

    .Update

    Else
    MsgBox "No such record...not updating."
    End If
End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Close the recordset and close the connection.
rst.Close
cnt.Close

Set cnt = Nothing

ExitSub:
Set rs = Nothing
'...and set to nothing if no such record
Exit Sub
</code></pre>

<hr>

<p>thank you,</p>

<p>hughv</p>