Two Hoots Banner



Recover Access Deleted TabletopTop of Page

Have you ever deleted an Access table ... and then suddenly realised that you made a mistake.

Well there are at least 2 ways to retrieve your deleted table-- one very simple, and another that requires code.

The first method is to try a simple Ctl-Z undo command. This process will only work if you haven't performed some other Cut, Copy, Paste or Undo combination of actions before trying to restore the table. If that fails, then you need to write code.

The code below may be copied and pasted into an Access Module and executed from the Immediate Window by executing this line of code

UnDeleteTable("MyTable")

There are some limits as to what may be restored ... and when. Here's the exception list:

The database has not been closed since the deletion of the table
The database has not been compacted since the deletion of the table
The table was deleted using the Microsoft Access user interface

Copy the code below and put it in your MS Access toolbox for when you accidentally deleted a table.

Function UnDeleteTable(Optional sName As String)

Dim db As DAO.DATABASE
Dim tdf As DAO.TableDef
Dim sTable As String
Dim sSQL As String
Dim sMsg As String

If IsMissing(sName) Then sName = "RestoredTable"
If Len(sName) = 0 Then sName = "RestoredTable"
    
Set db = CurrentDb()

For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) = "~tmp" Then
       sTable = tdf.Name
       sSQL = "SELECT [" & sTable & "].* INTO " & sName
       sSQL = sSQL & " FROM [" & sTable & "];"
        
       db.Execute sSQL
        
       sMsg = "A deleted table has been restored as " & sName
       MsgBox sMsg, vbOKOnly, "Restored"
       GoTo Exit_Undelete
    End If
Next
    
' If the code has fallen to this point, then no deleted
' tables exist in the catalog and none are recoverable.
MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_Undelete:
     Set db = Nothing
     Exit Function
     
Err_Undelete:
     MsgBox Err.Description
     Resume Exit_Undelete

End Function
Access to SQL Server Data TypestopTop of Page

The following table shows how SQL data types are displayed when the table is linked.

SQL ServerAccess
BinaryBinary
VarbinaryBinary
CharText
VarcharText
DatetimeDate/Time
SmalldatetimeDate/Time
DecimalText
NumericText
FloatDouble
RealSingle
IntInteger
SmallintInteger
TinyintInteger
IdentityAutonumber
MoneyCurrency
SmallmoneyCurrency
BitYes/No
TimestampBinary
TextMemo
ImageOLE Object


Putting an end of report message on the last page

topTop of Page
1. Place two invisible textboxes under the other controls in the Detail section.

2. txtCurrentRecord with Control Source =[CurrentRecord].

3. txtEndOfData with text = "End of Data"

4. Declare a global variable in a standard module

5. Public lngRecordCount AS Long

6. Set the variable in the report's Header_Format event.

7. lngRecordCount = DCount("*", "tblContacts")

8. Add the following code to the Report's Detail_Format event.

If lngRecordCount = Me![txtCurrentRecord].Value Then
  Me![txtEndofData].Visible = True
Else
  Me![txtEndofData].Visible = False
End If