Results 1 to 12 of 12
  1. #1
    amaklop is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6

    Delete corrupt table - Error database engine could not lock table

    Hi

    I have a table with a corrupt record (Chinese character in one of the columns). I'm trying write code to resolve this should it happen again. I know ... we should rather try to ensure the db doesn't become corrupted .. however ours still manage to be from time to time.

    So the corrupt record is GrnID = 3 in the T_GoodsReceivedNotes table. GrnID is the primary key, autonumber of the T_GoodsReceivedNotes table.

    So I figured if I make a copy of the corrupt table excluding the corrupt record then the copy of the table will be clean. Then I can drop the corrupt table, rename the temp table and Bob's your uncle.

    So I've managed to successfully :
    a) Identify the corrupt record
    b) Make a copy of the corrupt table excluding the bad record

    However when I try to drop the corrupt table I get the following error :

    The database engine could not lock table 'T_GoodsReceivedNotes' because it is already used by another person or process

    I did originally think it was as the error said being used by another person or process but after closing the back end database (the tables are in the back end by the way) as well as all the forms in the front end database it was still giving the error. I dropped another random table on the back end database just to check the db wasn't opened twice and it dropped successfully. So my conclusion is that it's not dropping it because of the bad record.

    Any ideas.

    Here is the code.

    Set db = OpenDatabase("C:\Matrix\Matrix_be.accdb")
    db.Execute "Select into Temp_GoodsReceivedNotes From T_GoodsReceivedNotes where GrnId <> " & RecKey


    db.Execute "drop table T_GoodsReceivedNotes"
    db.Execute "SELECT * INTO T_GoodsReceivedNotes FROM Temp_GoodsReceivedNotes"
    db.Execute "drop table Temp_GoodsReceivedNotes"
    db.Close

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    amaklop is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    Ahhh ... ok sorry ...

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, let someone else spend the time

    UA are usually pretty sharp at that

    I've withdrawn my post

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    amaklop, you were previously called out for cross posting here https://www.accessforums.net/showthr...537#post409537
    AND at AWF yet continue to do so. Doesn't seem to have sunk in.
    Good luck.

  7. #7
    amaklop is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    Thanks ... The compact and repair worked.

    How do you programatically compact and repair? I want to put a button on the front end that the user can click that will do what I just did on the back end database.

    If I create a new db and copy all tables accross except the corrupt table, what do I do with the corrupt table? If the compact and repair didn't work, how do you get rid of the corrupt record?

    Is it possible to give the user the ability to recover from the corrupt table by clicking a button and me doing this in the back end? I thought it would have worked well if I could have got the code to work

    Yes I do lose the indexing and default values of the tables. I have tried to add them again with this :

    dbs.TableDefs("T_GoodsReceivedNotes").Fields("Stat us").Properties("DefaultValue") = "New"



    And sorry the code I posted previously was wrong. This is the correct code :

    DoCmd.RunSQL "SELECT * INTO TempGoodsReceivedNotes in 'C:\Matrix\Matrix_be.accdb' FROM T_GoodsReceivedNotes where GrnId <> " & RecKey

    Set db = OpenDatabase("C:\Matrix\Matrix_be.accdb")
    db.Execute "drop table T_GoodsReceivedNotes"
    db.Execute "SELECT * INTO T_GoodsReceivedNotes FROM Temp_GoodsReceivedNotes"
    db.Execute "drop table Temp_GoodsReceivedNotes"
    db.Close

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I can't delete my post, but have edited it. Perhaps pointless since OP has probably had the email prompt, but there is a lot of it going on right now, I've wasted too many hours in the last few days providing detailed responses to find the reason the OP didn't understand what I was saying was because they were mixing in responses from other forums. So I am now going to add repeat offenders to my ignore list from now on (on this and the other forums) - and amak is the first on the list.

  9. #9
    amaklop is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    Sorry this was a post of a couple of weeks ago (before I knew what cross posting was ... )

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Deleted suggestion.
    Already resolved.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by amaklop View Post
    Sorry this was a post of a couple of weeks ago (before I knew what cross posting was ... )
    This post is from TODAY. You were called out for incorrectly cross posting a week ago 9/23.
    Cross posting isn't a sin. Doing so and not declaring it is. Not acknowledging it and continuing to do so is too.
    I was probably first to put you on a list.

    You might be able to redeem yourself in one or more forum members eyes...

  12. #12
    amaklop is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    Umm ... ok sorry ... I was away on holiday and didn't check the previous post again as it was resolved so I didn't see the cross-post call out on 9/23. I understand why you are frustrated. sorry again

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

Similar Threads

  1. Replies: 1
    Last Post: 06-08-2018, 05:19 PM
  2. Replies: 13
    Last Post: 01-26-2018, 01:27 PM
  3. Replies: 4
    Last Post: 09-13-2017, 02:29 PM
  4. Replies: 2
    Last Post: 07-14-2014, 10:34 AM
  5. Database engine could not lock table issue
    By ganeshvenkatram in forum Access
    Replies: 2
    Last Post: 06-28-2011, 11:41 PM

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