title: Updating MS Access record using Excel vba with record Id
body: <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>