Monday, May 4, 2009

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
Post a Comment