Sometimes, it can be useful to be able to determine if a table is local or not.

Yes, you can query the MsysObjects system table for those object with a Type = 1 as explained in my article:

but then you need to create a recordset, and iterate over it, …

Why do that when there’s a much simpler solution!

The Simpler Solution

Every linked table presents a connection string.  So we can simply directly check the individual table definition for the presence of a connection string.  If none exists, then you know it is a Local Table.  Otherwise, it is a Linked Table.

Knowing this, we can build the following function:

'---------------------------------------------------------------------------------------
' Procedure : Table_IsLocal
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a table is a Local, or not
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Name of the table to check if it is a local table, or not
'               True => Local table, False => Linked table
'
' Usage:
' ~~~~~~
' Table_IsLocal("UserSettings")
'   Returns -> True
' Table_IsLocal("Contacts")
'   Returns -> False
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-03-08              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function Table_IsLocal(sTable As String) As Boolean
On Error GoTo Error_Handler

    Table_IsLocal = (CurrentDb.TableDefs(sTable).Connect = "")
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    '3265 - Item not found in this collection. '*****Table doesn't exist
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Table_IsLocal" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Now, with a single line, you can test a table to know if it is a Local table, or not.

Usage Examples

So, you can use the function in any test you’d like, the following being a simple IF/Then/End If, where if the ‘UserSettings’ table is a Local table (True) then we can proceed to take another action.

If Table_IsLocal("UserSettings") = True Then
   '....
End If

A Few Resources on the Subject

TableDef.Connect property (DAO)

Sets or returns a value that provides information about a linked table. Read/write String.