Retrieve Information about Access using ADO2.1

Category:

Database

Type:

Classes

Difficulty:

Advanced

Author: Brian Gillham

Version Compatibility: Visual Basic 6

More information:

This class will enable the developer to retrieve all sorts of information about Access DataBases using ADO. NB You must set a reference to MSADOX in the Project References

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Option Explicit
'**************
'* Procedure  * DBCatalog
'* Purpose    * Retrieve information on Access Tables
'* Parameters *
'* Remarks    * Be sure to set Project References to ADOX
'* Created    * 99/05/02 11:28:31 AM
'**************
'Properties of the Catalog
Private Catalog As ADOX.Catalog
Private Col     As ADOX.Column
Private Cols    As ADOX.Columns
Private Grp     As ADOX.Group
Private Grps    As ADOX.Groups
Private Ndx     As ADOX.Index
Private Ndxs    As ADOX.Indexes
Private Key     As ADOX.Key
Private Keys    As ADOX.Keys
Private Proc    As ADOX.Procedure
Private Procs   As ADOX.Procedures
Private Prop    As ADOX.Property
Private Props   As ADOX.Properties
Private Table   As ADOX.Table
Private Tables  As ADOX.Tables
Private User    As ADOX.User
Private Users   As ADOX.Users
Private View    As ADOX.View
Private Views   As ADOX.Views

Public Enum TblProps
    tblTempTable = 0
    tblValidationText = 1
    tblValidationRule = 2
    tblCacheLinkNamePassword = 3
    tblRemoteTableName = 4
    tblLinkProviderString = 5
    tblLinkDataSource = 6
    tblExclusiveLink = 7
    tblCreateLink = 8
    tblTableHiddenInAccess = 9
End Enum

Public Enum ColProps
    colAutoincrement = 0
    colDefault = 1
    colDescription = 2
    colNullable = 3
    colFixedLength = 4
    colSeed = 5
    colIncrement = 6
    colValidationText = 7
    colValidationRule = 8
    colIISNotLastColumn = 9
    colAutoGenerate = 10
    colOneBlobPerPage = 11
    colCompressedUnicode = 12
    colAllowZeroLength = 13
    colHyperlink = 14
End Enum
'####################
'# PUBLIC FUNCTIONS #
'####################
Public Function ColumnFormat(vTable As Variant, vCol As Variant) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    Set Col = Table.Columns(vCol)
    ColumnFormat = NumberFormat(Col.Type)
End Function

Public Function ColumnFormatStr(vTable As Variant, vCol As Variant) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    ColumnFormatStr = NumberFormatStr(Table.Columns(vCol).Type)
End Function

Public Function ColumnProperty(vTable As Variant, vCol As Variant, PropEnum As ColProps) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    Set Col = Table.Columns(vCol)
    If Col.Properties.Count > 0 Then
        ColumnProperty = Col.Properties(PropEnum).Value
    End If
End Function

Public Function ColumnType(vTable As Variant, vCol As Variant) As ADOX.DataTypeEnum
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    ColumnType = Tables(vTable).Columns(vCol).Type
End Function

Public Function CreateTmpTable(tmpTable As String, Optional CopyTable As String, Optional rsClone As ADODB.Recordset) As Boolean
    On Error GoTo ErrHandler
    'First thing we do is delete the table if it exists
    If TableExists(tmpTable) Then
        Tables.Delete tmpTable
    End If

Set Table = New Table
    Set Table.ParentCatalog = Catalog

With Table
        .Name = tmpTable
        .Keys.Append "pkID", adKeyPrimary
        Set Cols = Tables(CopyTable).Columns
        For Each Col In Cols
            .Columns.Append Col.Name, Col.Type, Col.DefinedSize
            .Columns.Item(Col.Name).Properties(colDescription).Value = Col.Properties(colDescription)
        Next Col
    End With
    Tables.Append Table
    CreateTmpTable = True
Exit Function
ErrHandler:
    CreateTmpTable = False
End Function

Public Function CreateDB()
    Select Case App.Title
        Case "GoldWorX": Create_GoldWorX
    End Select
End Function

Public Function Datashape(rsParent As ADODB.Recordset, _
                          tblParent As String, _
                          tblChild As String, _
                          fldParent As String, _
                          fldChild As String, _
                 Optional idParent As Variant = "") As Boolean
    '=========================================================
    'This function will return a DisConnected SHAPEd RecordSet
    'Assumptions:
    '
    '
    '=========================================================
    On Error GoTo LocalError
    Dim lSQL        As String
    Dim lcnADO      As New ADODB.Connection
    Dim lrsParent   As New ADODB.Recordset

With lcnADO
        .Mode = adModeShareDenyNone
        .CursorLocation = adUseServer
        .Provider = "MSDataShape"
        .Open CNX
    End With
    'Define the SQL Statement
    lSQL = ""
    If idParent <> "" Then
        lSQL = "SHAPE  {select * from [@PARENT] WHERE [@PARENTFIELD] = " & idParent & "} " & vbCrLf
    Else
        lSQL = "SHAPE  {select * from [@PARENT]} " & vbCrLf
    End If
    lSQL = lSQL & "APPEND ({select * from [@CHILD]} RELATE [@PARENTFIELD] TO [@CHILDFIELD]) AS ChildItems"
    'Replace parsed parameters in the command string
    lSQL = Replace(lSQL, "[@PARENT]", tblParent)
    lSQL = Replace(lSQL, "[@PARENTFIELD]", fldParent)
    lSQL = Replace(lSQL, "[@CHILD]", tblChild)
    lSQL = Replace(lSQL, "[@CHILDFIELD]", fldChild)
    'Get the data
    rsParent.StayInSync = False
    rsParent.Open lSQL, lcnADO, adOpenStatic, adLockOptimistic
    Set rsParent.ActiveConnection = Nothing
    lcnADO.Close
    Set lcnADO = Nothing
    Datashape = True
Exit Function
LocalError:
    RaiseError Err.Number, Err.Source & " (DBCatalog.DataShape)", Err.Description
End Function

Public Function DSNFile() As String
    DSNFile = CNX
End Function

Public Function IsKey(vTable As Variant, vCol As Variant) As Boolean
    'On Error Resume Next
    Dim KeyCol
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    'Check for Index property - Access Primary Key is ALWAYS Indexed
    For Each Ndx In Table.Indexes
        If Ndx.PrimaryKey Then
            For Each Col In Ndx.Columns
                If Col.Name = vCol Then
                    IsKey = True
                    Exit Function
                End If
            Next Col
        End If
    Next Ndx
End Function

Public Function TableProperty(vTable As Variant, PropEnum As TblProps) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    TableProperty = Table.Properties(PropEnum).Value
    Set Table = Nothing
End Function

Public Function ADOTables() As ADOX.Tables
    Set ADOTables = Tables
End Function

'#####################
'# PRIVATE FUNCTIONS #
'#####################
Private Function Create_GoldWorX() As Boolean
    'set the Table Names
    Const Addresses    As String = "Addresses"
    Const Categories   As String = "Categories"
    Const Contacts     As String = "Contacts"
    Const Countries    As String = "Countries"
    Const CreditCards  As String = "CreditCards"
    Const Products     As String = "Products"
    Const SystemInfo   As String = "SystemInfo"
    Const Transactions As String = "Transactions"
    Const TRXitems     As String = "TRXitems"

Catalog.Tables.Append Addresses
    Set Table = Tables("Addresses")
    With Table
        '.Columns.Append "pkID", adNumeric
        '.Columns("pkID").Attributes
    End With
End Function

Private Function NumberFormat(ColType As ADODB.DataTypeEnum) As String
    Select Case ColType
        Case adEmpty                                                '  0 - No value was specified (DBTYPE_EMPTY).
        Case adSmallInt:         NumberFormat = "General Number"    '  2 - A 2-byte signed integer (DBTYPE_I2).
        Case adInteger:          NumberFormat = "General Number"    '  3 - A 4-byte signed integer (DBTYPE_I4).
        Case adSingle:           NumberFormat = "General Number"    '  4 - A single-precision floating point value (DBTYPE_R4).
        Case adDouble:           NumberFormat = "General Number"    '  5 - A double-precision floating point value (DBTYPE_R8).
        Case adCurrency:         NumberFormat = "Currency"          '  6 - A currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
        Case adDate:             NumberFormat = "General Date"      '  7 - A Date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
        Case adBSTR                                                 '  8 - A null-terminated character string (Unicode) (DBTYPE_BSTR).
        Case adIDispatch                                            '  9 - A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
        Case adError                                                ' 10 - A 32-bit error code (DBTYPE_ERROR).
        Case adBoolean:          NumberFormat = "True/False"        ' 11 - A Boolean value (DBTYPE_BOOL).
        Case adVariant                                              ' 12 - An Automation Variant (DBTYPE_VARIANT).
        Case adIUnknown                                             ' 13 - A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
        Case adDecimal:          NumberFormat = "Standard"          ' 14 - An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
        Case adTinyInt:          NumberFormat = "General Number"    ' 16 - A 1-byte signed integer (DBTYPE_I1).
        Case adUnsignedTinyInt:  NumberFormat = "General Number"    ' 17 - A 1-byte unsigned integer (DBTYPE_UI1).
        Case adUnsignedSmallInt: NumberFormat = "General Number"    ' 18 - A 2-byte unsigned integer (DBTYPE_UI2).
        Case adUnsignedInt:      NumberFormat = "General Number"    ' 19 - A 4-byte unsigned integer (DBTYPE_UI4).
        Case adBigInt:           NumberFormat = "General Number"    ' 20 - An 8-byte signed integer (DBTYPE_I8).
        Case adUnsignedBigInt:   NumberFormat = "General Number"    ' 21 - An 8-byte unsigned integer (DBTYPE_UI8).
        Case adGUID                                                 ' 72 - A globally unique identifier (GUID) (DBTYPE_GUID).
        Case adBinary                                               '128 - A binary value (DBTYPE_BYTES).
        Case adChar                                                 '129 - A String value (DBTYPE_STR).
        Case adWChar                                                '130 - A null-terminated Unicode character string (DBTYPE_WSTR).
        Case adNumeric:          NumberFormat = "General Number"    '131 - An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
        Case adUserDefined                                          '132 - A user-defined variable (DBTYPE_UDT).
        Case adDBDate:           NumberFormat = "General Date"      '133 - A date value (yyyymmdd) (DBTYPE_DBDATE).
        Case adDBTime:           NumberFormat = "Long Time"         '134 - A time value (hhmmss) (DBTYPE_DBTIME).
        Case adDBTimeStamp:      NumberFormat = "General Date"      '135 - A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
        Case adVarChar                                              '200 - A String value (Parameter object only).
        Case adLongVarChar                                          '201 - A long String value (Parameter object only).
        Case adVarWChar                                             '202 - A null-terminated Unicode character string (Parameter object only).
        Case adLongVarWChar                                         '203 - A long null-terminated string value (Parameter object only).
        Case adVarBinary                                            '204 - A binary value (Parameter object only).
        Case adLongVarBinary                                        '205 - A long binary value (Parameter object only).
    End Select
End Function

Private Function NumberFormatStr(ColType As ADODB.DataTypeEnum) As String
    Select Case ColType
        Case adEmpty:            NumberFormatStr = "Empty"              '  0 - No value was specified (DBTYPE_EMPTY).
        Case adSmallInt:         NumberFormatStr = "SmallInt"           '  2 - A 2-byte signed integer (DBTYPE_I2).
        Case adInteger:          NumberFormatStr = "Integer"            '  3 - A 4-byte signed integer (DBTYPE_I4).
        Case adSingle:           NumberFormatStr = "Single"             '  4 - A single-precision floating point value (DBTYPE_R4).
        Case adDouble:           NumberFormatStr = "Double"             '  5 - A double-precision floating point value (DBTYPE_R8).
        Case adCurrency:         NumberFormatStr = "Currerncy"          '  6 - A currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
        Case adDate:             NumberFormatStr = "Date"               '  7 - A Date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
        Case adBSTR:             NumberFormatStr = "BSTR"               '  8 - A null-terminated character string (Unicode) (DBTYPE_BSTR).
        Case adIDispatch:        NumberFormatStr = "IDispatch"          '  9 - A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
        Case adError:            NumberFormatStr = "Error"              ' 10 - A 32-bit error code (DBTYPE_ERROR).
        Case adBoolean:          NumberFormatStr = "Boolean"            ' 11 - A Boolean value (DBTYPE_BOOL).
        Case adVariant:          NumberFormatStr = "Variant"            ' 12 - An Automation Variant (DBTYPE_VARIANT).
        Case adIUnknown:         NumberFormatStr = "Unknown"            ' 13 - A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
        Case adDecimal:          NumberFormatStr = "Decimal"            ' 14 - An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
        Case adTinyInt:          NumberFormatStr = "TinyInt"            ' 16 - A 1-byte signed integer (DBTYPE_I1).
        Case adUnsignedTinyInt:  NumberFormatStr = "UnsignedTinyInt"    ' 17 - A 1-byte unsigned integer (DBTYPE_UI1).
        Case adUnsignedSmallInt: NumberFormatStr = "UnsignedSmallInt"   ' 18 - A 2-byte unsigned integer (DBTYPE_UI2).
        Case adUnsignedInt:      NumberFormatStr = "UnsignedInt"        ' 19 - A 4-byte unsigned integer (DBTYPE_UI4).
        Case adBigInt:           NumberFormatStr = "BigInt"             ' 20 - An 8-byte signed integer (DBTYPE_I8).
        Case adUnsignedBigInt:   NumberFormatStr = "UnsignedBigInt"     ' 21 - An 8-byte unsigned integer (DBTYPE_UI8).
        Case adGUID:             NumberFormatStr = "GUID"               ' 72 - A globally unique identifier (GUID) (DBTYPE_GUID).
        Case adBinary:           NumberFormatStr = "Binary"             '128 - A binary value (DBTYPE_BYTES).
        Case adChar:             NumberFormatStr = "Char"               '129 - A String value (DBTYPE_STR).
        Case adWChar:            NumberFormatStr = "WChar"              '130 - A null-terminated Unicode character string (DBTYPE_WSTR).
        Case adNumeric:          NumberFormatStr = "Numeric"            '131 - An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
        Case adUserDefined:      NumberFormatStr = "UserDefined"        '132 - A user-defined variable (DBTYPE_UDT).
        Case adDBDate:           NumberFormatStr = "DBDate"             '133 - A date value (yyyymmdd) (DBTYPE_DBDATE).
        Case adDBTime:           NumberFormatStr = "DBTime"             '134 - A time value (hhmmss) (DBTYPE_DBTIME).
        Case adDBTimeStamp:      NumberFormatStr = "DBTimeStamp"        '135 - A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
        Case adVarChar:          NumberFormatStr = "VarChar"            '200 - A String value (Parameter object only).
        Case adLongVarChar:      NumberFormatStr = "LongVarChar"        '201 - A long String value (Parameter object only).
        Case adVarWChar:         NumberFormatStr = "VarWChar"           '202 - A null-terminated Unicode character string (Parameter object only).
        Case adLongVarWChar:     NumberFormatStr = "LongVarWChar"       '203 - A long null-terminated string value (Parameter object only).
        Case adVarBinary:        NumberFormatStr = "VarBinary"          '204 - A binary value (Parameter object only).
        Case adLongVarBinary:    NumberFormatStr = "LongVarBinary"      '205 - A long binary value (Parameter object only).
        Case Else:               NumberFormatStr = "Ask Bill Gates"
    End Select
End Function

Private Function SetCatalog() As ADOX.Catalog
    'Retrieves the description of the field
    'Cat.Tables(1).Columns(1).Properties(2).Value
    'Set DBCatalog = Cat
    'Set Cat = Nothing
    If Not Catalog Is Nothing Then
    End If
End Function

Private Function TableExists(TableName) As Boolean
    On Error GoTo ErrHandler
    Set Table = Tables(TableName)
    TableExists = True
Exit Function
ErrHandler:
    TableExists = False
End Function

Private Function ColumnExists(ColName) As Boolean
    On Error GoTo ErrHandler
    Set Col = Table.Columns(ColName)
    ColumnExists = True
Exit Function
ErrHandler:
    ColumnExists = False
End Function

Private Sub Class_Initialize()
    'Create the Catlog
    Set Catalog = New ADOX.Catalog
    Catalog.ActiveConnection = CNX
    Set Tables = Catalog.Tables
    Set Users = Catalog.Users
    Set Views = Catalog.Views
    Set Procs = Catalog.Procedures
    Set Grps = Catalog.Groups
End Sub

Private Sub Class_Terminate()
    Set Col = Nothing
    Set Cols = Nothing
    Set Grp = Nothing
    Set Grps = Nothing
    Set Ndx = Nothing
    Set Ndxs = Nothing
    Set Key = Nothing
    Set Keys = Nothing
    Set Proc = Nothing
    Set Procs = Nothing
    Set Prop = Nothing
    Set Props = Nothing
    Set Table = Nothing
    Set Tables = Nothing
    Set User = Nothing
    Set Users = Nothing
    Set View = Nothing
    Set Views = Nothing
    Set Catalog = Nothing
End Sub