Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: List box causing locked table error

    I've added a list box to my form that displays a table. This table is completely independent and gains it's data from an Append query. There is also a Delete query, which clears the table for a new set of data, and an Alter query which resets an AutoNumber column in the table (so that new data sets always start numbered at 1).

    Here's the problem: Since I've added the listbox and set the RowSource to the table, whenever I run the Append query I receive the error: "The database engine could not lock table 'tblLineSheet' because it is already in use by another person or process." I've made sure to close the table, but to no avail I still get this error. Without the List Box I don't encounter this error.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is not normal. Using a table as the RowSource property of a listbox or a combobox does not creates a dynamic recordset (i.e. you cannot update the table from the combo or the list), so the table should not be locked (see attached example)

    How do you define the RowSource property of the control?
    Attached Files Attached Files
    Have a nice day!

  3. #3
    Join Date
    Jul 2014
    Posts
    2
    I really can't figure it out from what you suggested. If you'd like to give my file a look I'd really appreciate it.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The macro associaded with the button opens a DDL query (qryResetAutoNumb) then tries to open a DML query (qryDeleteData) while the first one is still open. More stangely, the list seems to lock the table, for some reason I don't understand. Instead of associating a macro to the Click event of the command button cmndDeleteTable, use a VBA procedure, like this:
    Code:
    Private Sub cmndDeleteTable_Click()
    
        Me.listboxLineSheet.RowSource = ""
        CurrentDb.Execute "qryResetAutoNumb", dbFailOnError
        CurrentDb.Execute "qryDeleteData", dbFailOnError
        Me.listboxLineSheet.RowSource = "SELECT tblLineSheet.* FROM tblLineSheet;"
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •