Deleted table

[Access SS]

I deleted a table out of my database... i know need it back. I still have
the database open, is there anyway to get the table back?

 

[Dirk Goldgar]

I deleted a table out of my database... i know need it back. I still have
the database open, is there anyway to get the table back?

If you haven't compacted or done anything else, this might work:

'------ start of code ------
Function UndeleteTable()

Dim db As DAO.Database, strTableName As String
Dim i As Integer, StrSqlString As String

Set db = CurrentDb()

For i = 0 To db.TableDefs.Count - 1

If Left(db.TableDefs(i).Name, 4) = "~tmp" Then
strTableName = db.TableDefs(i).Name
StrSqlString = "SELECT DISTINCTROW [" & strTableName & _
"].* INTO MyUndeletedTable FROM [" & strTableName & "];"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSqlString
DoCmd.SetWarnings True
MsgBox "A table has been restored as MyUndeletedTable", _
vbOKOnly, "Restored"
GoTo Exit_UndeleteTable
End If
Next i
MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_UndeleteTable:
Set db = Nothing
Exit Function
Err_UndeleteTable:
MsgBox Err.Description
Resume Exit_UndeleteTable

End Function
'------ end of code ------

You would paste that code into a standard module, and then either invoke it
via F5 or enter this in the Immediate Window:

UndeleteTable

 

[Maurice]

Nope, backup has to do the trick. That's why you are asked for confirmation
before the table is deleted.

Sorry... maybe someone does know a nifty trick somehow.

 

[a a r o n . k e m p f]

SQL Server could allow you restore up until right before this
happened.

with Access, you're stuck with the backup copy from last night

 

[Larry Linson]

SQL Server could allow you restore up until
right before this happened.

That is why SQL Server, in one of its several editions, is often used as the
database with an Access frontend in one of three versions: MDB, ACCDB, or
ADP.

with Access, you're stuck with the backup copy from last night

A solution which might work to recover the deleted table has been posted. If
that works, it's moot when the backup copy was made. There is no required
schedule for making backup copies, so Mr. Kempf's statement should read "the
most recent backup copy" (and should state, "if that is required").

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

SQL Server allows you to restore to a point in time.

You can take backups at midnight, and then restore everything up until
5pm.

_THAT_ is the feature that this user needs.

So why don't you go and argue with someone else, mental midget!

-Aaron