FYI: Creating and verifying Access Tables from scratch

[Lord_Rat]

I spent all day trying to make my program create a table dynamically and fix fields if they are not of a type I am expecting.

I couldn't find a case of someone already doing this, so this is an FYI post for any who are looking to do the same thing.

I like to keep my code as easy (for me) to use, so here is how you check a table:

'This code assumes a 'backup' folder exists under the sDataSet location
        Dim sDataSet As String = Application.ExecutablePath
        sDataSet = sDataSet.Remove(InStrRev(Application.ExecutablePath, "\"), Len(Application.ExecutablePath) - InStrRev(Application.ExecutablePath, "\")) 'defines the folder where the data is
        Dim tdTable As New TableDescription()
        With tdTable
            .PriKey = "PriKey" 'Primary Key field. Can be left blank
            .TableName = "Locations" 'Name of table to check
            .Fields = MakeArray("Location_Description", "Location_Database") 'field names
            .FieldTypes = MakeADOXArray(ADOX.DataTypeEnum.adVarWChar, ADOX.DataTypeEnum.adVarWChar) 'Field types of fields
            .MissingIsFatal = MakeBooleanArray(False, False) 'whether to throw an error is a field doesn't match
        End With
 
        VerifyDataBase("dataset.mdb", tdTable) 'Now verify the 'dataset.mdb' database

And the TableDescription Class...

Public Class TableDescription
    Private TName As String
    Private aFields() As String
    Private MFatal() As Boolean
    Private FTypes() As ADOX.DataTypeEnum
    Private PKey As String
 
    Property TableName() As String
        Get
            Return TName
        End Get
        Set(ByVal Value As String)
            TName = Value
        End Set
    End Property
 
    Property Fields() As String()
        Get
            Return aFields
        End Get
        Set(ByVal Value() As String)
            aFields = Value
        End Set
    End Property
 
    Property MissingIsFatal() As Boolean()
        Get
            Return MFatal
        End Get
        Set(ByVal Value() As Boolean)
            MFatal = Value
        End Set
    End Property
 
    Property FieldTypes() As ADOX.DataTypeEnum()
        Get
            Return FTypes
        End Get
        Set(ByVal Value() As ADOX.DataTypeEnum)
            FTypes = Value
        End Set
    End Property
 
    Property PriKey() As String
        Get
            Return PKey
        End Get
        Set(ByVal Value As String)
            PKey = Value
        End Set
    End Property
End Class