Useful schema related functions

Public Function ConstraintIsRelation(ConstraintName As String, dbs As DAO.Database) As Boolean

Is the constraint also a database Relation ? In MS Access, any foreign key constraint is associated with a DAO Relation object.


Public Function DbTableDelete(dbs As DAO.Database, TableName As String) As String

Delete the table, first removing all database relations involving it

Public Sub DeleteTableIndexesContainingColumn(dbs As DAO.Database, td As DAO.TableDef, ColName As String)

Delete all indexes on the table that contain the specified column

Public Function FieldHasFkRelation(TableName As String, FieldName As String, dbs As DAO.Database, Optional CheckForPrimary As Boolean = True, Optional CheckForForeign As Boolean = True) As Boolean

Does the field have an FK relationship with another table ?

Public Sub FixAutonumberCol(TableName As String, dbs As DAO.Database, Optional ForceFix As Boolean = False)

Reset the AutoNumber seed value for the table.
For Access linked tables, a bug means that inserting to the Autonumber column resets the autonumber seed to the value following that inserted.
FixAutonumberCol() sets it back to MAX(autonumbercolumn) + 1

Public Function GetAutonumberColName(TableName As String, dbs As DAO.Database) As String

Return the name of the table's AutoNumber column, or empty string if none

Public Function GetTableColList(TableName As String, dbs As DAO.Database, Optional ExcludePK As Boolean = False, Optional Delimiter As String = ", ") As String

Return the table's column names as a comma separated list.


Public Sub GetTablePrimaryKeyColNameCollection(TableName As String, dbs As DAO.Database, ColumnColl As Collection)

Return the column name(s) in the primary key as a collection.


Public Function GetTablePrimaryKeyColNameList(TableName As String, dbs As DAO.Database , Optional Delimiter As String = ", ", Optional AddAutonumAsterisk As Boolean = False) As String

Return the column name(s) in the primary key as a comma separated list.

Public Function IndexContainsField(i As DAO.index, FieldName As String) As Boolean

Is the column part of the passed table index ?

Public Function IndexFieldList(i As DAO.index, Optional WithAscDesc As Boolean = False) As String

Return the index's field/column names as a comma separated list.


Public Function RelationContainsField(R As DAO.Relation, FieldName As String, Optional UseForeignName As Boolean = False) As Boolean

Is the field/column part of the passed relationship ? In MS Access, any foreign key constraint is associated with a DAO Relation object.


Public Function RelationFieldList(R As DAO.Relation, Optional UseForeignName As Boolean = False) As String

Return the relation's field/column names as a comma separated list.
In MS Access, any foreign key constraint is associated with a DAO Relation object.


Public Function TableColumnInPrimaryKey(TableName As String, ColName As String, dbs As DAO.Database) As Boolean

Is the column name part of the primary key ?

Public Function TableIsAccessLinked(TableName As String, dbs As DAO.Database) As Boolean

Is the table an Access linked table ?

Public Function TurnOffSubDataSheets(TableName As String, dbs As DAO.Database)

Turn off table subdatasheets in the database since they cause corruption