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
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
