Get a List of Access Objects Using Visual Basic 2005

I am writing a program in visual basic 2005 that would perform a number of file system operations. Find the number of files on a drive, the number of folders on a drive or in a specific folder, and mainly find Old Files on a network drive.

I thought that it would be very beneficial to get information about Access Databases and Excel Spreadsheets on the drives as well to eliminate duplicates.

I have a button on the main form of the program (screen shot will be provided soon) that searches the drive for all Access Database (another snippet of code about that later). The program fills a list box with the names of all the Access Databases it finds in a specific location. From the list, I wanted the user to be able to click a button and then get a complete list of tables, queries, modules, and other objects in that database. I wanted this to be done instantly.

This can be achieved in one of two ways:

First Method
1. Click Project.
2. Click Add Reference...
3. Click the COM Tab
4. Select "Microsoft ActiveX Data Objects 2.5 Library"
5. Select "Microsoft ADO Ext. 2.8 for DDL and Security"
6. Click Ok

Use the following Code:

Imports ADODB
Imports ADOX

Dim conn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim Table As ADOX.Table
Dim Qry As ADOX.View
Dim DBName As string

DBName = "C:\temp.mdb"

conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & DBName & " ")
cat.ActiveConnection = AccConn


For Each Table in cat .Tables

lstBox.Items.Add(Table.Name)

lstBox.Items.Add(Table.Type)

Next


For Each Qry in cat.Views

lstBox.Items.Add(Qry.Name)

lstBox.Items.Add(Qry.Command)

Next

Second Method
The second method uses a SQL Query to the Access Database that reads the information directly from the table called MSysObjects, MySysAccessObjects, MySysQueries.

These are system tables that can be queries directly.

for example
SELECT

MsysObjects.Id,

MsysObjects.ForeignName,

MsysObjects.DateCreate,

MsysObjects.Database,

MsysObjects.Connect,

MsysObjects.Name,

MsysObjects.Owner,

MsysObjects.Type

FROM

MsysObjects;

This Code will give you the list of all objects in the System Objects table. You can filter it according to what you're looking for. Then use that information inside of your VB Code. (Another Snippet coming later).