Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    40

    Unanswered: A locked table while trying to delete it

    Hi, I am in the process of creating a search mechanism for my database. On the results page I have a subform in datasheet view to display the actual results. This is bound to a temporary table which stores the search results, which is called "tmpResults". The table will have different columns, depending on the search which is being performed, so I delete the table and recreate it at the start of processing the results. My problem is that since the subform in datasheet view is bound to the form, it won't let me delete the table because it thinks it is locked. Here is an example of what I mean.

    Code:
    Public Sub frmResults_Open
      ' Protect delete statement in case table doesn't exist
      On Error Resume Next
        CurrentProject.Connection.Execute "DROP TABLE tmpResults;"
      On Error GoTo 0
    
      ' {Insert code to create the string CREATEstr, which is an SQL create statement for tmpResults}
      CurrentProject.Connection.Execute CREATEstr
    
      '...
    End Sub
    However the create statement does not execute, since the table already exists. Commenting out the "Resume Next" line reveals the error message:

    "The database engine could not lock table `tmpResults` because it is already in use by another person or process"

    Now I assume that this message is coming up because the RecordSource of the results subform is set to tmpResults. But I don't know how to get rid of this lock! Could you help me?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I would create one form to enter search criteria in. That form would be unbound, and have an "Execute Filter" command button. That would drop and recreate the table, and then open the bound subform displaying your filtered data.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by RedNeckGeek
    I would create one form to enter search criteria in. That form would be unbound, and have an "Execute Filter" command button. That would drop and recreate the table, and then open the bound subform displaying your filtered data.
    This is exactly the setup I have got. However this doesn't really answer my question.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The way I read your question is that you have the bound subform open while trying to drop the table. I'm saying open the subform only after you've rebuilt the table.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Once I have build a temporary table, I never delete it again, but just clear the records from it, then do an append query. And of course these two operations can be done with the subform already open.

    Another process would be to make the RecordSource for the subform blank until after you have rebuilt the temporary table, then reset the RecordSource to your temporary table name.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by GolferGuy
    Once I have build a temporary table, I never delete it again, but just clear the records from it, then do an append query. And of course these two operations can be done with the subform already open.
    Hi Vic

    I think the complication here are the dynamic columns.

    I'm liking the recordsource solution though. Easy peasy.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thanks pootle! I just reread the initial post. I had missed the "The table will have different columns" That makes my first suggestion rather silly.

  8. #8
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by GolferGuy
    Another process would be to make the RecordSource for the subform blank until after you have rebuilt the temporary table, then reset the RecordSource to your temporary table name.
    Yep, that worked, thanks again guys!

Posting Permissions

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