Three Ways to Skin an Access Cat of Tables

I agree: The title makes almost no sense at all.  But that's ok.  I rarely make sense to anyone, even myself, but it's all good.

This post shows the SAME chunk of code, doing the exact same thing, done in three languages.  All for the price of ONE!  Free!  That's right: Free!   And if you act now, I'll even throw in a vegetable slicer (just kidding).

The core of this mess is using the ADOX COM interface library to query a Microsoft Access database file (.mdb or .accdb usually) to spit out the list of Table objects, and for each Table show the names of the Columns within it.  You can drill down further, but I'm too lazy, so this is simply meant to be a fairly decent starting point.  The idea is to see how each scripting language looks while doing the same thing, which helps for comparison and familiarity training.  Cheers!

VBScript Example

'****************************************************************
' Filename..: adox_samples.vbs
' Author....: skatterbrainz
' Date......: 05/04/09
' Purpose...: display MS-access db schema and table info
' SQL.......: \\server\path\dbfile.mdb (or) c:\dbfile.mdb
'****************************************************************

Const dbfile = "c:\dbfile.mdb"

Sub ListTablesADOX(db)
  Dim Conn, strConn, Catalog, Table, Column
   
  strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & db
   
  Wscript.Echo "Database: " & db & vbCRLF
   
  Set Conn    = CreateObject("ADODB.Connection")
  Set Catalog = CreateObject("ADOX.Catalog")
  Set Table   = CreateObject("ADOX.Table")
  Set Column  = CreateObject("ADOX.Column")
   
  Conn.Open strConn
  Set Catalog.ActiveConnection = Conn

  For Each Table In Catalog.Tables
      Wscript.Echo "Table: " & Table.Name
      For Each Column In Table.Columns
          Wscript.Echo vbTab & "Column: " & Column.Name
      Next
  Next
  Conn.Close
End Sub

ListTablesADOX dbfile

KiXtart Example

;****************************************************************
; Filename..: adox_samples.kix
; Author....: skatterbrainz
; Date......: 05/04/09
; Purpose...: display MS-access db schema and table info
; SQL.......: \\server\path\dbfile.mdb (or) c:\dbfile.mdb
;****************************************************************

$dbfile = "c:\dbfile.mdb"

Function ListTablesADOX($db)
  Dim $Conn, $strConn, $Catalog, $Table, $Column

  $strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$db"
   
  ? "Database: " + $db + @CRLF
   
  $Conn    = CreateObject("ADODB.Connection")
  $Catalog = CreateObject("ADOX.Catalog")
  $Table   = CreateObject("ADOX.Table")
  $Column  = CreateObject("ADOX.Column")

  $Conn.Open($strConn)
  $Catalog.ActiveConnection = $Conn

  For Each $Table In $Catalog.Tables
      ? "Table: " + $Table.Name
      For Each $Column In $Table.Columns
          ? Chr(9) + "Column: " + $Column.Name
      Next
  Next
  $Conn.Close
EndFunction

$=ListTablesADOX($dbfile)

PowerShell Example*

#****************************************************************
# Filename..: adox_samples.ps1
# Author....: skatterbrainz
# Date......: 05/04/09
# Purpose...: display MS-access db schema and table info
# SQL.......: \\server\path\dbfile.mdb (or) c:\dbfile.mdb
#****************************************************************

$dbfile = "c:\dbfile.mdb"

Function ListTablesADOX($db) {

  $strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source="+$db

  write-host "Database: " + $db + "`n"

  $Conn    = new-object -ComObject ADODB.Connection
  $Catalog = new-object -ComObject ADOX.Catalog
  $Table   = new-object -ComObject ADOX.Table
  $Column  = new-object -ComObject ADOX.Column

  $Conn.Open($strConn)
  $Catalog.ActiveConnection = $Conn

  ForEach ($Table In $Catalog.Tables) {
      write-host "Table: " + $Table.Name
      ForEach ($Column In $Table.Columns) {
          write-host "`tColumn: " + $Column.Name
      }
  }
  $Conn.Close()
}

ListTablesADOX($dbfile)

* Built and tested on PowerShell 2.0 CTP3 on Windows XP Pro SP2 with Office 2007