by Brian Gillham (7 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 26th June 1999
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Retreive ALL the information about Access Table & Fields using ADO. Be sure to make a reference to ADOX in your Project.
API Declarations
'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
VBcoders
'return variant because we do not
'know the type of data that is going
'to be returned to calling method
On Error GoTo ErrHandler
Set Table = Tables(TableName)
Set Cols = Table.Columns
Set Col = Cols(Column)
ColumnFormat = NumberFormat(Col.Type)
ExitHere:
Set Table = Nothing
Set Cols = Nothing
Set Col = Nothing
Exit Function
ErrHandler:
ColumnFormat = ""
Resume ExitHere
End Function
Public Function ColumnProperty(TableName As String, Column As Variant, Property As ColProps) As Variant
'return variant because we do not
'know the type of data that is going
'to be returned to calling method
On Error GoTo ErrHandler
Set Table = Tables(TableName)
Set Cols = Table.Columns
Set Col = Cols(Column)
ColumnProperty = Col.Properties(Property).Value
ExitHere:
Set Table = Nothing
Set Cols = Nothing
Set Col = Nothing
Exit Function
ErrHandler:
ColumnProperty = ""
Resume ExitHere
End Function
Public Function TableProperty(TableName As String, Property As TblProps) As Variant
'return variant because we do not
'know the type of data that is going
'to be returned to calling method
On Error GoTo ErrHandler
Set Table = Tables(TableName)
Set Props = Table.Properties
TableProperty = Table.Properties(Property).Value
ExitHere:
Set Table = Nothing
Set Props = Nothing
Exit Function
ErrHandler:
TableProperty = Nothing
Resume ExitHere
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 adUnsignedBigInt: NumberFormat = "General Number" ' 21 - An 8-byte unsigned integer (DBTYPE_UI8).
Case adBigInt: NumberFormat = "General Number" ' 20 - An 8-byte signed integer (DBTYPE_I8).
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 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 Sub Class_Initialize()
'Create the Catlog
Set Catalog = New ADOX.Catalog
Catalog.ActiveConnection = cnADO
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
No comments have been posted about Retreive ALL the information about Access Table & Fields using ADO. Be sure to make a reference to . Why not be the first to post a comment about Retreive ALL the information about Access Table & Fields using ADO. Be sure to make a reference to .
