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