How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Pam R Your Own Question

Pam R
Pam R, Computer Specialist
Category: Microsoft Office
Satisfied Customers: 68
Experience:  13 yrs+ supporting Office issues
37043666
Type Your Microsoft Office Question Here...
Pam R is online now
A new question is answered every 9 seconds

I deleted an Access database file by mistake, how can I get

This answer was rated:

I deleted an Access database file by mistake, how can I get it back please? I meant to delete one line only, but it deleted the whole thing. Arghh!

ptw123234 :

Hi, my name is XXXXX XXXXX I will try to assist you today with your problem, please let me know when you are ready

Customer:

HI Pam, ready!

ptw123234 :

Hi, first of all did you work on the database for a long time, I ask because it may have saved along the way which we may be able to restore. Also what is the name of your database please

Customer:

Hi, no I was not working on it a long time. I just opened it up to delete someone who we had been notified had just died. I copied this person from the main database (called OSA:Table) onto our list of deceased members (which we keep so that they can be mentioned at the AGM), and then tried to delete the entry from OSA:Table but instead deleted the whole Dec-11 (deceased in 2011) file.

ptw123234 :

so its the table you have deleted rather than the database is that correct?

Customer:

Sorry I don't understand the difference. I am not trained in Access, just inherited the software and files when the previous person was taken ill, and I am trying to look after it until he is better. The main database is OK and I ahve been able to delete the deceased person. However the other file, perhaps a table or a sub-database, containing the people who have been deleted during 2011, is gone.

ptw123234 :

ok, thank you it seems you have deleted the table. Have you closed the database since you deleted the table, unfortunately if you have it may not be retrievable as usually the only way you can restore a deleted table is before you close the database.

Customer:

I haven't closed the database, I realised that doing anything to it may be a bad thing, so it is still open and I have not done any actions on it.

ptw123234 :

ok can you first try just simply pressing ctrl z which is the simple undo command

ptw123234 :

thats ctrl and z at the same time

Customer:

OK have tried and nothing happened

ptw123234 :

which version of access are you running do you know

Customer:

Microsoft Access 2003

ptw123234 :

please can you bear with me a moment, there may be a way we can do this. I just need to check something first. Also can you please save your database using a different name to a different location but do not close it.

ptw123234 :

I may be about 10 minutes

Customer:

Thanks, XXXXX XXXXX copy

ptw123234 :

I am still looking at some code that we can use to retrieve this please bear with me

ptw123234 :

I have some code that we can use to try and restore the table it is a little complicated is the procedure, do you want to try it?

Customer:

i'll try it

ptw123234 :

  1. in the Database window, click Modules under Objects, and then click New.


 

Customer:

OK thats' been done

ptw123234 :

Type or paste the following code in the module that you have just created:

ptw123234 :



  1. Function RecoverDeletedTable() On Error GoTo ExitHere '*Declarations* Dim db As DAO.Database Dim strTableName As String Dim strSQL As String Dim intCount As Integer Dim blnRestored As Boolean '*Init* Set db = CurrentDb() '*Procedure* For intCount = 0 To db.TableDefs.Count - 1 strTableName = db.TableDefs(intCount).Name If Left(strTableName, 4) = "~tmp" Then strSQL = "SELECT DISTINCTROW [" & strTableName & "].* INTO " & Mid(strTableName, 5) & " FROM [" & strTableName & "];" DoCmd.SetWarnings False DoCmd.RunSQL strSQL MsgBox "A deleted table has been restored, using the name '" & Mid(strTableName, 5) & "'", vbOKOnly, "Restored" blnRestored = True End If Next intCount If blnRestored = False Then MsgBox "No recoverable tables found", vbOKOnly End If '*EXIT/ERROR* ExitHere: DoCmd.SetWarnings True Set db = Nothing Exit Function ErrorHandler: MsgBox Err.Description Resume ExitHere End Function




 

ptw123234 :

sorry

ptw123234 :

just bear with me

ptw123234 :

dont copy the text,

ptw123234 :

just need to find a way to get the code to you in the correct line formats,,,, this window doesnt allow it

Customer:

Hi Pam

Customer:

You have my email address - would that help?

Pam R and 2 other Microsoft Office Specialists are ready to help you
Function RecoverDeletedTable()
On Error GoTo ExitHere

'*Declarations*
Dim db As DAO.Database
Dim strTableName As String
Dim strSQL As String
Dim intCount As Integer
Dim blnRestored As Boolean

'*Init*
Set db = CurrentDb()

'*Procedure*
For intCount = 0 To db.TableDefs.Count - 1
strTableName = db.TableDefs(intCount).Name
If Left(strTableName, 4) = "~tmp" Then
strSQL = "SELECT DISTINCTROW [" & strTableName & "].* INTO " & Mid(strTableName, 5) & " FROM [" & strTableName & "];"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
MsgBox "A deleted table has been restored, using the name '" & Mid(strTableName, 5) & "'", vbOKOnly, "Restored"
blnRestored = True
End If
Next intCount

If blnRestored = False Then
MsgBox "No recoverable tables found", vbOKOnly
End If

'*EXIT/ERROR*
ExitHere:
DoCmd.SetWarnings True
Set db = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Description
Resume ExitHere

End Function
cut and paste the above code into the module that you just created please

from
function recover
to
end function
Hi, have you managed to cut and paste the code?
Customer: replied 5 years ago.

HI Pam

 

Your above two requests did not reach me, so I logged out and in again, and now have seen them. Yes, I have now pasted the code into the module.

if you do not have a little window at the bottom of the screen which is called 'immediate' then click on view at the top of the screen and select 'immediate window'
Customer: replied 5 years ago.

There is no option that says immediate window - where should I look?

 

 

Customer: replied 5 years ago.
OK, found it under "view" just like you said, sorry., Have clicked on "Immediate window"
do you have a window at the bottom that is called 'immediate'
Customer: replied 5 years ago.

Yes, it has opened at the bottom

 

ok, great...
Can you type in the 'immediate window' the following

RecoverDeletedTable

please note that you MUST use the capitals where I have. When you do that you will or should get a message saying that table or tables have been recovered and will give you a name that it has given to the recovered table. It will not have the same name it did before but we can change that

dont forget to press enter when you have typed

RecoverDeletedTable
Customer: replied 5 years ago.
A pop-up says no recoverable tables found
Oh dear... that code would certainly have recovered any tables it could. Are you sure you have not closed down the database since you deleted the table. That is the only reason the table would not be there to recover
Customer: replied 5 years ago.
Absolutely sure, I didn't touch anything because I didn't want to spoil my chances of getting it back
it appears that the table is unrecoverable. The code did not give any error it simply said there were no tables to recover so it worked but just couldnt recover the table. I am sorry you were not able to get this back. I have checked online and this is the only code or method available to carry out this recovery.
Customer: replied 5 years ago.

OK thanks for your help. I am devastated at my error - I am in so much trouble.

 

I will continue to research this on your behalf and if anyone comes up with another way I will contact you also.