none
I get sporadic errors "Operation must use an updateable query"

    Question

  • I have a Visual Studio (VB) application that runs in a desktop environment using an Access data base that is located in the Public\Documents folder.  The program uses the following connection string:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Documents\DanceWorks\DBase01.accdb

    Data records are inserted and  updated via SQL statements  that use statements similar to the following:

    "UPDATE School SET SRegNo='" & RegNo & " WHERE SchID=1"

     "UPDATE Account SET Balance="  & setAcctBalance & ", LastPmtDate=#"  & LDate & "#, LastPmtAmt="  & Math .Abs(LAmt) & " WHERE AcctID="  & AcctID  

    After the error occurs if I restart the program and perfrom the same function it works without error.  

    How can I eliminate these errors, or what can I do (perhaps in a Try .. Catch statement) to eliminate this error and get the data updated correctly?

     Thanks  


    Dale Roedger
    • Edited by DaleRoedger Friday, February 03, 2012 3:50 PM
    Friday, February 03, 2012 3:41 PM

Answers

  • It could be one of several things. 

    If the same query run multiple times, one time it works, 2nd time it fails, then it is most likely a database engine locking issue.   The Jet engine uses extended file locks on the associated lock file for the database to control concurrency on the main file.   You can hit locking issues if multiple processes are using the file at the same time.   If it is the same process, you could have multiple threads accessing the same file for example.   If it is a single threaded single process with a single connection to the database, I would not expect this to happen.

    There are some tools out there that look at the locks, see for example this => http://www.fmsinc.com/MicrosoftAccess/monitor.asp

    You could also try this technique with the new driver -> http://support.microsoft.com/kb/285822

    As well as try LDBView tool => http://support.microsoft.com/kb/176670

    Short of this I would expect some issue with query inputs due to concatenation.   It could be that due to certain inputs the Jet query optimizer decides that it cannot update things but your query looks very simple.

     


    Matt
    Friday, February 03, 2012 9:33 PM

All replies

  • My theory is this is due to your use of concatenation of SQL.   Concatenation will fail for example if the value passed in contains single quotes or # sign.

    For example, suppose LDate value accidentally has a # sign in it, or RegNo has a quote in it.

    The correct approach here is to use parameterized commands and pass the values in using parameters.   This will also avoid SQL injection issues with your application which is the #1 cause of security bugs in database code.

    To debug, you can print out the final SQL statement when your exception is hit.  This will probably clearly show you what is going wrong.  But my strongest suggestion is learn how to use parameters to pass in these values it will save you many debugging and security heartaches in the long run.

     


    Matt
    Friday, February 03, 2012 6:42 PM
  • You didn't indicate whether there are multiple concurrent users for this database but that could also be an issue. Problems either creating or modifying the corresponding .laccdb file can result in the database not being updateable.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, February 03, 2012 7:25 PM
  • I was including the two SQL statements as examples of the type of statement that caused the problem.  My estimation is that they are pretty ordinary.

    I know the values that are in the variables in those SQL statements.  Plus the statements work 99% of the time.

    The question is what causes them to fail 1% of the time.  Also is there something that can be done in the Try .. Catch block to get a valid SQL statement to work when this error occurs.


    Dale Roedger
    Friday, February 03, 2012 8:08 PM
  • There is one user (me) in my development environment.

    I had been updating records in various tables successfully when suddenly I could no longer insert a record in the Transaction table.

    How do I check the corresponding .laccdb file?  What am I looking for in it?


    Dale Roedger
    Friday, February 03, 2012 8:12 PM
  • It could be one of several things. 

    If the same query run multiple times, one time it works, 2nd time it fails, then it is most likely a database engine locking issue.   The Jet engine uses extended file locks on the associated lock file for the database to control concurrency on the main file.   You can hit locking issues if multiple processes are using the file at the same time.   If it is the same process, you could have multiple threads accessing the same file for example.   If it is a single threaded single process with a single connection to the database, I would not expect this to happen.

    There are some tools out there that look at the locks, see for example this => http://www.fmsinc.com/MicrosoftAccess/monitor.asp

    You could also try this technique with the new driver -> http://support.microsoft.com/kb/285822

    As well as try LDBView tool => http://support.microsoft.com/kb/176670

    Short of this I would expect some issue with query inputs due to concatenation.   It could be that due to certain inputs the Jet query optimizer decides that it cannot update things but your query looks very simple.

     


    Matt
    Friday, February 03, 2012 9:33 PM