none
action queries 'unfairly' triggering debugger RRS feed

  • Question

  • Hi,

    I would highly appreciate some help with the following weird problem.

    I used Access 2010 to make a small app. The very first form runs a series of queries, some 28. There are make table,  append and delete queries. All goes without a hitch EVERY  time. 

    Then, one would proceed and enter/edit data. This requires to update tables, again. So, ALL forms are closed and it should go back to do the same queries as at the start.

    However, the VERY SAME sequence is halted by the debugger when it comes to either the make table queries or the append queries. The error message says

    Run-time error "3211". The database engine could not lock the table [target table name] because it is already used by another person or process.

    The code is the following (cut out all lines except two to avoid useless repetition)

    Private Sub Form_Load()
        DoCmd.SetWarnings False
    DoCmd.OpenQuery "qrCountDeadline 1- 2", acViewNormal, acEdit (make table query)
    DoCmd.OpenQuery "qrCountDeadline 1- 3", acViewNormal, acEdit (append query)
        DoCmd.SetWarnings True
    End Sub

    THANKS for any suggestion.


    • Edited by Jjoey Wednesday, February 8, 2017 4:36 AM
    Wednesday, February 8, 2017 4:35 AM

Answers

  • That seems to be a problem, but maybe you did not sprinkle:
    db.tabledefs.refresh

    Which will refresh the tabledefs collection. I have to tell you YEARS go by that I don't use a MakeTable query. Are you REALLY sure it's appropriate, as opposed to Delete and Append?


    -Tom. Microsoft Access MVP

    • Marked as answer by Jjoey Thursday, February 9, 2017 3:17 PM
    Wednesday, February 8, 2017 4:53 AM

All replies

  • That seems to be a problem, but maybe you did not sprinkle:
    db.tabledefs.refresh

    Which will refresh the tabledefs collection. I have to tell you YEARS go by that I don't use a MakeTable query. Are you REALLY sure it's appropriate, as opposed to Delete and Append?


    -Tom. Microsoft Access MVP

    • Marked as answer by Jjoey Thursday, February 9, 2017 3:17 PM
    Wednesday, February 8, 2017 4:53 AM
  • Thank you, Tom, for the suggestion. I modified the structure to do all with append queries and thing returned to normal: it WORKS. Best regards...
    Thursday, February 9, 2017 3:22 PM