locked
Cannot Update ACCESS 2007 data in Windows 7 RRS feed

  • Question

  • Created a Windows 2007 add-in using VSTO 2008.  Add-In uses a Windows Form to retrieve and update data in an ACCESS 2007 database.  Uses ADODB.  Add-in works fine in Windows XP.  I have installed the add-in on a Windows 7 pc.   Data retrieval works fine.  Data update does not work in Windows 7.  Receive the message "Operation must use an updateable query." 

    Public Sub RunSQL()

    Dim cn As ADODB.Connection = Nothing

     

    Try

    cn =

    New ADODB.Connection

    cn.Mode = ConnectModeEnum.adModeReadWrite

    cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Progam Files\MyApplication\MyDatabase.accdb;Persist Security Info=False")

    cn.Execute("Insert into RegionalData(ReportID, RegionalData) Values(1,'SomeRegionalData')")

    cn.Close()

     

    Catch ex As Exception

    WriteExceptionLog(ex.Message, ex.StackTrace,

    "DBHelper.RunSQL", strSQL)

     

    Finally

    cn =

    Nothing

     

    End Try

     

    End Sub

    Everything works fine in Windows XP.  Data updates do not work in Windows 7.  Data retrieval works in Windows 7.

    Monday, February 1, 2010 9:32 PM

Answers

All replies

  • Hello

    I find several articles that analyze the possible causes of the error:
    http://blogs.msdn.com/jongallant/archive/2009/04/06/solution-to-operation-must-use-an-updateable-query-exception-when-writing-to-an-access-database-from-asp-net.aspx
    http://support.microsoft.com/kb/175168
    http://support.microsoft.com/default.aspx/kb/316475

    The causes that are related to this case is: 

    Please check whether the db file is marked as readonly on your Win7 PC. In addition, does the user who is running the above code has enough permission to write to the db file? Also, can you try adding "ReadOnly=0" to the connection string?


    Regards,
    Jialiang Ge
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 2, 2010 8:26 AM
  • Hello

    How are you? Could you please check out my last reply and let me know whether it's helpful to you?
    Regards,
    Jialiang Ge
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, February 5, 2010 7:43 AM
  • Thank you.  I will check the links out today.  The db file is not marked as readonly on the W7 PC when I check the properties, however, when I open the db file it opens as READONLY with some options blocked.  I will try adding the to the connection string.

    The only way I was able to get the application to write to the db file was to turn off UAC.  That is not a good solution.  I tried to move the db file to the USERS directory but then I was not able to modify the config file.  The user account has admin privileges but cannot do anything.  Last resort in effort to allow the user to test the application was to turn of UAC.  The application and db file are installed to C:\PROGRAM FILES\MYAPPLICATION.

    I am using ADODB, not ODBC so do not have an ODBC DSN

    The first link - .mdb file is not readonly when I check the properties but when I open the database it opens readonly and with some options blocked; I don't believe the application pool applies to my particular issue but I do believe it is a permissions issue

    Second link - I do have the mode in the connection (cn.Mode = ConnectModeEnum.adModeReadWrite)

    Third link - Adding READONLY=0 resulted in the error message COULD NOT FIND INSTALLABLE ISAM

    Tried this:
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Progam Files\MyApplication\MyDatabase.accdb;Persist Security Info=False;ReadOnly=0;"

    And this:
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Progam Files\MyApplication\MyDatabase.accdb;Persist Security Info=False;ReadOnly=0"

    with the same error message.

    Since I have had to run off the UAC in order to get the application to work, I believe that the UAC is the cause of the problem.  I need to know how to build the installer so that it will install the database piece in the right location....what is the right location?  Or, what setting do I place in the config file or the manifest to keep it in the Program Files location but allow it to write.  I am doing some testing with the manifest now.
    Friday, February 5, 2010 1:16 PM
  • UPDATE:  I found informations as to where to install the database portion of my application.  I installed the database portion of my application to C:\users\public\mycompany\myapplicationname.  This is in accordance with a couple links that I found.

    http://social.msdn.microsoft.com/Forums/en-US/windowscompatibility/thread/db6b0847-9781-4d11-860f-1568e35cf5de

    http://msdn.microsoft.com/en-us/windows/dd203105.aspx

    Installing the database portion of my application to the C:\program files\mycompany\myapplication is not a best practice for windows vista and w7 with the UAC.  In order to comply with the UAC, needed to install to the user directory.  By installing the database to the c:\users\public\mycompany\myapplicationname I did not have to modify the manifest to run as administrator.
    • Marked as answer by PNTB Monday, February 8, 2010 1:07 PM
    Monday, February 8, 2010 1:07 PM