Many thanks to the folks who sent helpful suggestions about this issue. After spending several days experimenting with their suggestions, I thought I’d report briefly on the results.
(1) Using the Access MSysObjects table. This works fine, but there are a couple of prerequisites needed before a VB.NET program can use MSysObjects. For any Access database that you plan to investigate, you must first make System objects visible. Select Tools | Options and on the View tab, check the System Objects checkbox in the Show group. You must also grant read permission for the MSysObjects table by selecting Tools | Security | User and Group Permissions, and checking the Read Data checkbox forthis table. If you only need to look at one or two Access tables, the prerequisites aren’t a problem; however, I was attempting to write a general-purpose metadata reporting application that could be used for any database a user could designate.
(2) Using the GetOleDbSchemaTable method for OleDbConnections. This method turned out to be much more convenient, since no permission granting is needed, and the same process can be used for obtaining metadata from a number of different DBMSs (not just Access). All you need to do is establish an OleDbConnection using the appropriate type of connection string for the database provided. (You can find a great list of connection string examples at
Once you have a connection, use the GetOleDbSchemaTable method as follows:
Code:
Dim schemaDT As DataTable
Dim thisNull As System.DBNull()
schemaDT = thisConn.GetOleDbSchemaTable( _
OleDbSchemaGuid.Tables, thisNull)
The DataTable that is returned from this statement is the collection of information about tables, but there are many other collections available, to wit: assertions, catalogs, character_sets, check_constraints, check_constraints_by_table, collations, column_domain_usage, column_privileges, columns, constraint_column_usage, constraint_table_usage, DBInfoLiterals, foreign_keys, indexes, key_column_usage, primary_keys, procedure_columns, procedure_parameters, procedures, provider_types, referential_constraints, schemata, sql_languages, statistics, table_constraints, table_privileges, table_statistics, tables, tables_info, translations, trustee, usage_privileges, view_column_usage, view_table_usage, and views.
Note that if you use the columns collection, the values in the column DATA_TYPE within that collection vary according to the kind of database to which you are connected. To find out how to interpret the DATA_TYPE values, check the provider_types collection, for example:
Code:
Dim typeDT As DataTable = _
thisConn.GetOleDbSchemaTable( _
OleDbSchemaGuid.Provider_Types, thisNull)
Dim msg as String
Dim cols As Integer = typeDT.Columns.Count
Dim rows As Integer = typeDT.Rows.Count
For i = 0 To rows - 1
Dim dataType As String = IIf(IsDBNull( _
typeDT.Rows(i).Item("DATA_TYPE")), _
"Null", _
typeDT.Rows(i).Item("DATA_TYPE"))
Dim typeName As String = IIf(IsDBNull( _
typeDT.Rows(i).Item("TYPE_NAME")),_
"Null", _
typeDT.Rows(i).Item("TYPE_NAME"))
msg = msg & CStr(dataType) & ": " & _
CStr(typeName) & " "
Next
MsgBox(msg)
With a DataTable containing the appropriate collection, it is relatively easy to loop through and display desired metadata information for Access and other DBMSs. Differences among data types that are significant to a particular DBMS may not be important for OLE’s handling of them. For instance, OLE handles SQL Server’s CHAR, TEXT, and VARCHAR as the same datatype (#129) and makes no distinctions among them when listing metadata.