Use VBA to determine if a table can be edited

Question

I have the following procedure in Form_Load:

Dim accObject As Access.AccessObject

For Each accObject In CurrentData.AllTables
    Me.cboSelectTable.AddItem accObject.Name
Next

This populates the combo box with ALL the tables including system tables and those linked to SQL Server. The problem is, I am only hoping to get back tables which can be edited. How can this be determined using VBA?

score 1 · Accepted Answer · answered Dec 05 '13 at 15:36

When retrieving your set of target table names you can ignore system tables (Like "MSys*") and temporary tables (Like "~*"). And ignore linked tables by checking whether the Connect property is anything other than a zero-length string.

Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
    With tdf
        If Not (.Name Like "~*" Or .Name Like "MSys*") Then
            If Len(.Connect) = 0 Then
                Me.cboSelectTable.AddItem .Name
            End If
        End If
    End With
Next
Set tdf = Nothing

If that doesn't give you everything you want, you can also check whether tdf.Updatable is True for each table.

Use VBA to determine if a table can be edited

1 Answers1