Results 1 to 9 of 9
  1. #1
    TFisher is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    2

    "Close form" action does not release table

    Hello all,



    Using an event procedure that calls a macro when the user clicks the "Finished" button, I am attempting to 1) copy table to a different database and then 2) delete the table.

    The copy works fine, but upon attempting the delete I get the error 3211 saying the table cannot be locked because it is in use by another person or process.

    I'm baffled and feel like a real novice! What do I need to do to release this table??? This should be easy

    Here is the code:
    Private Sub Finished_Click()

    DoCmd.Close acForm, "Open", acSaveYes

    stDocName = "Exit"
    DoCmd.RunMacro stDocName

    DoCmd.Quit acSave

    End Sub


    And here are the actions in the macro Exit:
    CopyObject (the table the form Open is bound to - works fine)
    DeleteObject (the table the form Open is bound to - halts to error 3211)


    (I'm surprised Access will copy a table that [it thinks] is in use but won't delete it.)

    Thanks for any help.
    Terri

  2. #2
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by TFisher View Post
    Hello all,


    I'm baffled and feel like a real novice! What do I need to do to release this table??? This should be easy

    Terri
    Try closing all forms first.

  3. #3
    TFisher is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    2
    Thanks. I only had the one form open, and that gets closed first thing. Ugh.

  4. #4
    Join Date
    May 2010
    Posts
    339

    Try it in two steps.

    Hi TFisher,

    It might be that your macro running in the back ground has the table locked...Here is what I might try.

    -Run your macro that copies your table ONLY. (remove rest of code)
    -Create a new button that performs the rest of the tasks.
    -Behind the new button put code that closes your form and kills the table. Then it closes the DB.

    Or
    Would you be happy with just empty the table? That can be done NP.

    Richard

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I have used this code on the onclose event of the form.
    1) I have two mdb files Sample1 and Sample2
    2) Sample2 Has a table Table1

    I have cleared the Recordsource of the form and thus the form is no longer bound to the Table and hence the delete is possible.


    Private Sub Form_Close()
    Dim strSQL As String
    Me.RecordSource = ""
    DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Sample2.mdb", acTable, "Table1", "Table1", False
    strSQL = "Drop Table Table1"
    CurrentDb.Execute strSQL, dbfailonerror
    End Sub



    if this solves your problem mark the thread solved.

  6. #6
    Join Date
    May 2010
    Posts
    339
    Nice job maximus,

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    if this thread has helped you to solve your problem mark this solved. If you have solved it in some other way post it here and mark the thread solved.

  8. #8
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    mark this thread solved

  9. #9
    mlrucci is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Apr 2018
    Posts
    202
    I was having a similar problem. Had a popup form using the tbl in question. In code, I closed the form and wanted to delete the temp table. This kept giving me an error 3211 locked table. I worked for a couple of hours trying to find the form that may be using the table. NONE. Read this post. Thought it would be helpful to others as I set the recordsource of the pop up to "" and then closed the form. This severed the link to the table and allowed me to delete the temp table. Thanks for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-13-2010, 10:50 AM
  2. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  3. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM
  4. Replies: 21
    Last Post: 06-03-2009, 05:54 PM
  5. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums