none
MS Access table locked error during attempt to create a foreign key reference RRS feed

  • Question

  • I've referenced content of several tables (say T1 and T2) via a join to create a DataSet object that is the data source for a datagridview.  
    Via a dialog that uses the DGV, the user creates a new row in the DataGridView which in turn requires an insert to one of the tables ("T1") and then the creation of a new table in the database.  The insert to "T1" executes successfully in one transaction.  And the Table create DDL executes correctly in another transaction until it encounters an attempt to reference "T1" (to build a relationship).

    The statement throws an exception that says that the table is locked because it is being used by another person or process.  This is a single-user app - so no one else has the table open.

    How can I avoid the lock error?  I assume that the problem is caused by either the DGV datasource that uses TI or the INSERT to T1.  Is it possible to create the SQL for either of these differently so that TI can be referenced by the DDL?
    • Moved by Harry Zhu Wednesday, September 9, 2009 7:14 AM relating to databinding (From:Visual C# General)
    • Moved by Kira Qian Thursday, September 10, 2009 3:12 AM (From:Windows Forms Data Controls and Databinding)
    Tuesday, September 8, 2009 6:17 AM

All replies

  • Hi Dean,

    How is the problem going now?   One possibility is that the T1 data table is locked by the Databinding progress (some db command locks it), then if we try to use another db command to update the T1 data table, the exception is thrown. 

    Could you please provide us with more detailed information about how perform the databinding and the insert operation?   Which data provider are you using?  

     

    Hope you have a nice day!

     

    Best Regards,
    Lingzhi Sun


    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.
    Monday, September 14, 2009 6:27 AM
    Moderator
  • I use databinding to bind the dataset to a datagridview.  So this may be the source of the problems.  (OleDb against an Access database)

    In the interest of time, I eliminated the table level referential integrity DDL in the create table statements and am handling this Cascade Delete part of the process manually.

    At this point, finding a solution is strictly an academic exercise. When I have time, I'll go back and try to find a solution.

    But thanks for your offer of assistance.

    How can I close this?
    Monday, September 14, 2009 4:20 PM