Obtaining MS Access Schema Information

[Headwinds]

I would like to be able to use VB.NET to search MS Access databases for basic schema information (table names, column names, etc.). I can get the names of columns in an Access table by connecting to an Access database, defining a select statement like "SELECT * FROM [tablename]", returning the results to a DataSet, and then looping through the DataSet's Columns collection. However, I can't find any way to determine what tables are contained in an Access database. I found one thread in this forum which mentioned using a select statement against "MSysObjects," but MSysObjects doesn't seem to be a known table or view in my Access databases. Any suggestions? Thanks in advance.

 

[krisbrixon]

See the following link with an idea of your MSysObjects.

http://p2p.wrox.com/archive/ado_dotnet/2002-09/4.asp

The following is a link to VB6 code to view table names.

http://www.a1vbcode.com/app.asp?ID=1237

Kris

 

[Grunt2002]

Hello,

You should have a look at the GetOleDbSchemaTable function of the OleDbDataReader class.
The functions accepts guid constants as an argument and returns a datatable that contains schema information.
The guid constants of OleDbSchemaGuid that determine the info to be retrieved are not all supported by Access.
However, the most useful do (list of tables, list of columns, list of keys).
There is very few information available on this subject over the net. What I did was running a loop that printed every datatable returned by the function enumerating the guid constants. Doing so, you will see how the schema information is structured by the oledb provider for .net (the one from which you are requesting schema information).
I think it is wiser to use oledb to get access to the metadata.

Cheers,

Grunt

 

[Headwinds]

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

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm.)

Once you have a connection, use the GetOleDbSchemaTable method as follows:

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:

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.