title: Operation Must Use An Updateable Query Error with Access 2007 and SQL 2005 Backend
body: <p>I'm using VB code in Access 2007 with a SQL 2005 backbone. My SQL server resides on a separate machine and the code below runs on a client. The problem is when I run the UPDATE command below I get an error "Operation Must Use An Updateable Query". I know this is common, but all my research on the internet didn't produce anything similar to my situation.</p>
  
  <pre><code>Dim MyDB as DAO.Database
  Dim sSQL as String
  sSQL = "UPDATE tblBMS_Batch SET Status = 70 WHERE Batch_ID = 108582"
  MyDB.Execute sSQL
  </code></pre>
  
  <p>The table set up is below</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[tblBMS_Batch](
  [Batch_Id] [int] IDENTITY(1,1) NOT NULL,
  [SDetail_Id] [int] NULL,
  [Destination_Id] [int] NULL,
  [Batch_Number] [smallint] NULL,
  [Line_Number] [smallint] NULL,
  [Packing_Number] [smallint] NULL,
  [Start_Time] [datetime] NULL,
  [End_Time] [datetime] NULL,
  [Status] [smallint] NULL,
  [Batch_Note] [varchar](1000) NULL,
  [Employee_Number] [smallint] NULL,
  [PLC_BatchNumber] [smallint] NULL,
  [Packing_BatchLetter] [varchar](3) NULL,
  [Number_Bags] [smallint] NULL,
  [Abort_Status] [bit] NULL,
  [Return_Bags] [smallint] NULL
  ) ON [PRIMARY]
  
  GO
  SET ANSI_PADDING OFF
  </code></pre>
  
  <p>The error occurs on the Execute. To make it more confusing the exact same UPDATE works in SQL directly, and I can also update that table in other VB code in a separate application on the same machine (albeit using ADODB connection).</p>
  
  <p>Any ideas? I'm pretty stumped here. Thanks in advance.</p>
  
  <p>Jason</p>