Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Unhappy Unanswered: A2003 - Can't delete a table in a macro

    Within macro1 I am:
    - closing 2 forms that that use table1
    - using an append query from table1 to table2
    - closing everything, (query and both tables)
    - deleteobject table1

    Macro1 fails at the last step with the error "Could not lock table1 because it is in use by another person or process"

    I've put some delay in macro1 prior to deleteobject line, but it still fails.

    If I remove the last line of the macro1 I can delete the table manually immediately after macro1 completes successfully. I can also successfully run macro2 that contains only the deleteobject line immediately after running macro1. However, it doesn't work if I call macro2 from macro1.

    I've wasted hours trying to get this to work.
    Thank you, Joe

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Tried it with VBA?

    I never use macros.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2009
    Posts
    5

    Re using VBA

    Unfortunately, I don't know a bit of VBA.
    Can anyone tell me a simple away around this issue?
    Thanks,
    Joe

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Oh not this cop-out again!! Time to learn a bit of VBA! It's not THAT hard. I actually find macros harder than VBA!

    No serious developer uses macros, so I doubt anyone here is going to just give you a simple workaround.

    Don't get me wrong here, I hope they do and you get an easy answer, but I just have my doubts that it will happen.

    While you're waiting for the miracle response, maybe check out VBA

    Check out the DoCmd. object (such as DoCmd.Close) which gives you macro actions that you'd be familiar with.

    Good luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Hi

    I have something very similar within one of my earlier projects, and all works fine the only difference I see is you dont need to close the tables down, for once the apend has taken place from Tbl 1 - Tbl 2, the focus has been taken of them, the other is you dont need to call the process from another macro, it works quite fine within one, see attached doc for my simple work around.

    Hope this helps
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2004
    Posts
    2

    Autoexec

    Maybe it's referenced in the Autoexec, which opens at application startup.
    Try deleting the referenced link from there first, then close access, then attempt to delete it.

  7. #7
    Join Date
    Oct 2009
    Posts
    8
    Just trying to duplicate what you where doing I quickly created a database with form 1 and form 2 bound both of these forms to data (table) then created an append query which I appended from data to data 2.

    I then wrote a query with 9 lines

    1. Opened form 1
    2. opened form 2
    3. Opened Query 1
    4. The next for lines where simple close commands closing Forms 1 then Form 2 then closing the data table and data2 table then closing the query.
    5. The last line in the query I used deleteobject to delete the data table.

    This did not hang up at all. This was loosely created based on your information. This probably does not help but it will work. Perhaps you good post a copy of your database for us to look at.

  8. #8
    Join Date
    Oct 2009
    Posts
    5
    Ok Mulefeathers here is the infamous database. You can recreate my issue by doing the following:
    1 Open the form PCP
    2 Click Go in front of "Select Items To Deploy (Resume)"
    (I've already checked some items so you can just...)
    3 Click the button at the bottom "Deploy All Selected Items"
    This will invoke the macro "AppendItemsToWO"
    At this point I get the error "Could not lock Deployable Items because it is in use by another person or process"

    If you want to delete Deployable Items table to start fresh go ahead. Recreate it by clicking Go in front of "Select Items To Deploy (Fresh Start)"
    then click a few check boxes on either of the forms before proceeding to step 3 above.
    I hope you find the issue right away. Good Luck.
    Thank you,
    Joe

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as with Startrekker I don'tuse macros, unless I have to.

    however I suspect you may have a problem depending on where you call the macro. if you attempt to delete a table from a form which is bound to the table you will get the symptoms you report.
    you may need to open say a special form, close the calling form then delete the table. you will need to examine current open objects to see if they are on your hit list. to make that task simpler you could consider closing all open forms except say your menu form and your 'special' form
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2009
    Posts
    5
    Thank you. I'll give this a try today.
    Joe

  11. #11
    Join Date
    Oct 2009
    Posts
    5

    Smile

    The problem was in fact just as described in healdem's post. I was calling for the table deletion from a macro triggered on a form bound to this table. Even though the macro was closing the form first, Access considered the form as a user of the table. Once called from elsewhere the problem was solved.

    Thank you so much for the insight.

    Joe

Posting Permissions

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