Error checking/handling (1 Viewer)

[matt beamish]

I have put this code together to delete linked tables if they exist, so that tables from a different back-end can be linked in, as required. I know my understanding of error handling is poor. Would anyone please have a look at what i have written and comment on where I need to improve? If the table does not exist, I want the procedure to continue.

thanks in advance

Private Sub Command1_Click()

Dim strT_Samplelistsite
Dim strT_Contexts
Dim strT_Cuts
Dim str_T_Subgroup

On Error Resume Next
strT_Samplelistsite = CurrentDb.TableDefs(T_Samplelistsite)
If TableExists = True Then
DoCmd.DeleteObject acTable, "T_Samplelistsite"
Else: TableExists = False
    End If

strT_Contexts = CurrentDb.TableDefs(T_Contexts)
If TableExists = True Then
DoCmd.DeleteObject acTable, "T_Contexts"
Else: TableExists = False
    End If

strT_Cuts = CurrentDb.TableDefs(T_Cuts)
If TableExists = True Then
DoCmd.DeleteObject acTable, "T_Cuts"
Else: TableExists = False
    End If

strT_Subgroup = CurrentDb.TableDefs(T_Subgroup)
If TableExists = True Then
DoCmd.DeleteObject acTable, "T_Subgroup"
Else: TableExists = False
    End If
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
ErrorCode:
 MsgBox Err.Description


End Sub

 

[smig]

On error resume next will prevent any error handling, as it will move to next line of code.
You should put:
On error goto Err_Command1_Click
And put it just at the beginning of the cole
What is the ErrorCode part for ?

 

[matt beamish]

Thanks for reply.
The Error Code bit is left over from me grafting together other examples and not cleaning up after myself.

thanks again

 

[gemma-the-husky]

Matt

I struggled to find this thread. Here's some code that might help

 Option Compare Database
Option Explicit
  
 Sub MyConnectTables()
'gemma-the-husky
  
 'I have a table called linkedtables, with a column called ourtable.
'I create a table called ourtable in the front end,, linked to a table called the same in the back end
'you can make this more complicated if you like. The name of the table in the front end need not be the same in the back end
'the backend tables can be in different backends.
'
'I tend to add counts to the code, to repoort how many tables were reconnected.
'I also tend to make this a boolean function so I can react accordingly if the reconnect fails
'
'Finally, see the use of the persistent connection after the first table is relinked.
'
'As an alternative some developers prefer to refresh the links, rather than delete and rebuild.
  
 Const usepath = "C:\pathtobackend"
Dim db As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim temprst As Recordset
Dim first As Boolean
 droptables:
    
    'open a recordset of the linkedtables table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("linkedtables")
    
    rst.MoveFirst 'not strictly necessary
    While Not rst.EOF
        On Error GoTo notconnected
        'you may need error handling in case the table has already been deleted.
        'you may also want to deal with tables that should be linked, but are not
    
        If CurrentDb.TableDefs(rst!ourtable).Connect <> "" Then
            DoCmd.RunSQL "drop table [" & rst!ourtable & "]"
        End If
dropnext:
        rst.MoveNext
    Wend
    
'now all the tables have been dropped
'now connect them all again
 reconnect:
    first = True
    rst.MoveFirst
    While Not rst.EOF
    On Error GoTo badconnect
          
        Set tdf = db.CreateTableDef(rst!ourtable)
        tdf.Connect = ";DATABASE=" & usepath    'see const above, or get it from a ini file etc.
        tdf.SourceTableName = rst!ourtable
        ' Append to the database's TableDefs collection
        db.TableDefs.Append tdf
        
        'this is a special add on
        'it creates a persistent connection to the first table we connected
        'otherwise connection can take a long while if youu are not the only current user.
        If first Then
            first = False
            Set temprst = db.OpenRecordset(rst!ourtable)
        End If
            
connectnext:
        rst.MoveNext
    Wend
     temprst.Close
    Set temprst = Nothing
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
    MsgBox ("Relink Completed")
Exit Sub
 
notconnected:
    'drop failed
    Resume dropnext
 badconnect:
    'connection failed
    Resume connectnext
 End Sub