Delete linked table

[Guest]

Thanks for taking the time to read my question.

I can't find a way to delete a linked table in code. I've searched in the
help, but found nothing.

Any suggestions?

Thanks again,

Brad

 

[Jeff Conrad]

in message:

Thanks for taking the time to read my question.

I can't find a way to delete a linked table in code. I've searched in the
help, but found nothing.

Any suggestions?

Copy and paste this code into a new standard module Brad:

'************Code Start************
Public Sub DeleteLink(strTableName As String)
On Error GoTo ErrorPoint

' Deletes the specified linked table

Dim dbs As DAO.Database

Set dbs = CurrentDb()

With dbs.TableDefs
If (.Item(strTableName).Attributes And dbAttachedTable) <> 0 Then
.Delete .Item(strTableName).Name
End If
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
Set dbs = Nothing
Exit Sub

ErrorPoint:
If Err.Number = 3265 Then
MsgBox "The specified table cannot be found." _
, vbExclamation, "Table Not Found"
Else
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
End If
Resume ExitPoint

End Sub
'************Code End************

Then just call it like this:

Private Sub Command0_Click()
DeleteLink ("Table Name Here")
End Sub

 

[Justin Hoffman]

in message:


Copy and paste this code into a new standard module Brad:

'************Code Start************
Public Sub DeleteLink(strTableName As String)
On Error GoTo ErrorPoint

' Deletes the specified linked table

Dim dbs As DAO.Database

Set dbs = CurrentDb()

With dbs.TableDefs
If (.Item(strTableName).Attributes And dbAttachedTable) <> 0 Then
.Delete .Item(strTableName).Name
End If
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
Set dbs = Nothing
Exit Sub

ErrorPoint:
If Err.Number = 3265 Then
MsgBox "The specified table cannot be found." _
, vbExclamation, "Table Not Found"
Else
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
End If
Resume ExitPoint

End Sub
'************Code End************

Then just call it like this:

Private Sub Command0_Click()
DeleteLink ("Table Name Here")
End Sub

Although it is good that the sub won't delete a local table - it doesn't
alert anyone to the fact that an attempt was made to delete a local table.
A possible modification would be:

If {is linked table} Then
{Delete it}
Else
{Warn that it is local and can't be deleted}
End If