“Operation must use an updateable query” error in JET

When performing update queries against a Microsoft JET database, you may come across the following error:

Operation must use an updateable query

The Microsoft Knowledge Base has an article for the most obvious cause of this error, that the database file is not writable. However, you may also receive this error when trying to run an UPDATE query which contains a subquery:

UPDATE [Orders]
   SET [Status ID] = ( SELECT TOP 1 [Status ID]
                         FROM [Orders Status]
                        ORDER BY [Status ID] );

I encountered this error when trying to write an update query that was valid JETSQL and TSQL. Unfortunately it’s not possible to write the query in a way that is valid SQL on both platforms1 – to work around this error in JET you need to change the way the query is structured:

UPDATE [Orders]
       , ( SELECT TOP 1 [Status ID]
             FROM [Orders Status]
            ORDER BY [Status ID] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Status ID];

The query now runs, but we’re not out of the woods yet. Let’s try writing a similar query that would achieve the same thing:

UPDATE [Orders]
       , ( SELECT MIN([Status ID]) AS [Min Status ID]
             FROM [Orders Status] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Min Status ID];

This query does not run – it fails with the same error. The problem is our use of an aggregate function (in this case, MIN).

To work around this we must take a departure from ANSI SQL and use the Domain Aggregate functions in Access.

UPDATE [Orders]
   SET [Status ID] = DMin("[Status ID]", "[Orders Status]")

This works, and can even be extended to emulate a correlated subquery via each domain aggregate function’s optional criteria argument.

1 JETSQL and TSQL have differing syntax for update queries. TSQL requires you to use the FROM clause when the update involves multiple tables, JETSQL does not (in line with ANSI SQL). Thus to write an update query that works on multiple platforms you need to use a subquery.

Update

Turns out there is a Knowledge Base article for this problem, for Access 2.0, and describes 3 workarounds. The post above describes the third workaround. Note that the first workaround may not work if you’re inside a transaction (it uses DDL), so I recommend looking at workarounds two and three.

“Add Web Reference” in Visual Studio 2008

Visual Studio 2005 and earlier editions provide excellent support for consuming SOAP web services in any Visual Studio language. Unfortunately, later versions have removed some of this functionality for C++ projects, placing more burden on the developer consuming a web service from C++.

However, if you’re trying to consume a web service from C++ in Visual Studio 2008, it is capable of doing most of the heavy lifting for you, albeit in a more cumbersome fashion.

Let’s take a look at what Visual Studio 2005 offered for consuming SOAP web services in C++, and what later versions do not.

Web References in Visual Studio 2005

In Visual Studio 2005 there is an option to Add Web Reference in the context menu of a Visual C++ project. If you click on it you will be presented with a dialog similar to the one below:

Add Web Reference Dialog

Add Web Reference Dialog

Using this dialog you can find a SOAP web service and Visual Studio will download the discovery and web service definition files. When building your project Visual Studio will invoke SPROXY, a utility that is part of ATL Server. SPROXY generates C++ code for accessing the web service.

For C++ projects, Visual Studio 2005 added a web reference by downloading the discovery and web service definitions files to a folder inside the project and then adding a file filter. The filter’s Unique Identifier property is set to the URL of the web service. After the initial download you can right click on the filter and select Update Web Reference and Visual Studio will fetch the updated definition files. This is shown below.

Update Web Reference Dialog

Update Web Reference Dialog

Web References in Visual Studio 2008

In Visual Studio 2008 this dialog is no longer available for C++ projects, but the underlying functionality remains in the IDE.

To add a web reference to a C++ project in Visual Studio 2008 you will need two things: a copy of ATL Server and your web service definition files.

ATL Server is not included in Visual Studio 2008, although the IDE will still try to invoke SPROXY (and produce an error). You can download the source code from CodePlex and build the SPROXY project. You should add the include folder under Additional Include Directories for any projects that will use ATL. If you are using ATL frequently, copy the include folder somewhere static and add it as an Include files folder under Tools, Options, Projects and Solutions, VC++ Directories.

Make sure that sproxy.exe is added to your PATH, so Visual Studio can find it.

Because the IDE no longer downloads and generates the web service definition files automatically, you have to create these files yourself. At a minimum you will require a .discomap file in addition to the .wsdl and .disco files referenced by it. I recommend using Disco, as it will create all three files. Disco is available from the Visual Studio 2008 Command Prompt.

Once you have SPROXY in place it is a simple matter of adding a new filter to your project, populating the Unique Name property and then adding the three files discussed above. Visual Studio will automatically invoke SPROXY to generate the code your application needs to interact with the web service.

Visual Studio 2008 removes the Update Web Reference option from the context menu, so you will have to update your definition files manually if they change.

Web References in Visual Studio 2010

In Visual Studio 2010 it appears that Microsoft have removed Proxy generator from the list of build tools. While the documentation for Visual Studio 2008 has Proxy generator as step number three, the documentation for Visual Studio 2010 omits this tool. A possible workaround is to write a custom build rule for .discomap files that calls SPROXY.

Error 155005 in Xcode 3

When using Xcode with source code control you may encounter this following message when committing your project:

Error: 155005 (Working copy not locked; this is probably a bug, please report)

In my instance this was caused by placing the build folder under source code control. I use Subversion for source code control, which places a .svn folder inside each folder that is under its control. Xcode was deleting the contents of the build folder when compiling the project, thus deleting the .svn folder and confusing Subversion.

The solution was to remove the build folder from the SVN repository.

Charles Minnow has the writeup, along with an update for Subversion’s config file to make sure it doesn’t happen again:

### Section for configuring miscelleneous Subversion options.
[miscellany]
### Set global-ignores to a set of whitespace-delimited globs
### which Subversion will ignore in its 'status' output, and
### while importing or adding files and directories.
global-ignores = build *.mode1 *.pbxuser *~.nib .DS_Store *~