Categories

View Access table definitions part 2

 

Following on from the last post we wanted to be able to look at the table definitions

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
$datatype = DATA {
ConvertFrom-StringData -StringData @'
3 = Integer
7 = Date
130 = String
'@

}
function Get-AccessTableDefinition {
param (
    [string]$name,
    [string]$path,
    [string]$table = ""
)
    $file = Join-Path -Path $path -ChildPath $name 
    if (!(Test-Path $file)){Throw "File Does Not Exists"}

    $conn = New-Object -ComObject ADODB.Connection
    $conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $file")
    $cat = New-Object -ComObject ADOX.Catalog
    $cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
    if ($table) {
        $actable = $cat.Tables | where {$_.Name -eq $table}
        $actable.Columns | Format-Table Name, DefinedSize, 
        @{Name="Data Type"; Expression={$datatype["$($_.Type)"]}}  -AutoSize
    }
    else {$cat.tables | select Name, DateCreated, DateModified}
       
    $conn.Close()
}

 

We modify our function by defining a hash table to contain the data types and adding a table parameter to the function.

We test if the table parameter has been specified and if so get the particular table and dump the column information.  Note the use of the calculated expression for the column data type.  I’ve only covered the three types I know are in this table.  The others will be added later or see http://msdn.microsoft.com/en-us/library/ms675318(VS.85).aspx if you can’t wait.

The function can be used like this:

PS> Import-Module accessfunctions -Force
PS> Get-AccessTableDefinition -name test03.mdb -path c:\test

Name                                    DateCreated                             DateModified
----                                    -----------                             ------------
MSysAccessStorage                       23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysACEs                                23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysNameMap                             30/11/2009 10:44:44                     30/11/2009 10:44:44
MSysNavPaneGroupCategories              23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneGroups                       23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneGroupToObjects               23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneObjectIDs                    23/11/2009 17:22:58                     23/11/2009 17:22:58
MSysObjects                             23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysQueries                             23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysRelationships                       23/11/2009 17:18:33                     23/11/2009 17:18:33
test1                                   23/11/2009 17:22:41                     10/01/2010 15:47:10

PS> Get-AccessTableDefinition -name test03.mdb -path c:\test -table test1

Name      DefinedSize Data Type
----      ----------- ---------
DOB                 0 Date
FirstName         255 String
ID                  0 Integer
LastName          255 String

 

Technorati Tags: ,,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>