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

Customer Question

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!
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Pam R replied 2 years ago.

ptw123234 :

Hi, my name is XXXXX XXXXX X 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, Computer Specialist
Category: Microsoft Office
Satisfied Customers: 68
Experience: 13 yrs+ supporting Office issues
Pam R and 4 other Microsoft Office Specialists are ready to help you
Expert:  Pam R replied 2 years ago.
What Happens Now?

Your chat has ended, but you can still work with your Expert to get an answer to your question if you have not yet received one.

Come back to this page at any time to see additional information from your Expert.  You will also receive an email when your question is updated.  If you want to send a message to your Expert, use the box below.

If you have already received a satisfactory answer to your question, click the Accept button above.  Experts are credited for each accepted answer they provide.
Expert:  Pam R replied 2 years ago.
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
Expert:  Pam R replied 2 years ago.
cut and paste the above code into the module that you just created please

from
function recover
to
end function
Expert:  Pam R replied 2 years ago.
Hi, have you managed to cut and paste the code?
Customer: replied 2 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.

Expert:  Pam R replied 2 years ago.
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 2 years ago.

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

 

 

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

Yes, it has opened at the bottom

 

Expert:  Pam R replied 2 years ago.
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

Expert:  Pam R replied 2 years ago.
dont forget to press enter when you have typed

RecoverDeletedTable
Customer: replied 2 years ago.
A pop-up says no recoverable tables found
Expert:  Pam R replied 2 years ago.
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 2 years ago.
Absolutely sure, I didn't touch anything because I didn't want to spoil my chances of getting it back
Expert:  Pam R replied 2 years ago.
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 2 years ago.

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

 

Expert:  Pam R replied 2 years ago.
I will continue to research this on your behalf and if anyone comes up with another way I will contact you also.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional