View Access table definitions

Richard's profileRichard Siddaway's BlogPhotosBlogLists

< Previous Next >

February 03

View Access table definitions

We’ve looked quite a bit at how we can work with Access databases but how do we investigate the structure of the database. The first thing we need to know is the tables in our database

   001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
 function Get-AccessTableDefinition {
param (
    [string]$name,
    [string]$path
)
    $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
    $cat.tables | select Name, DateCreated, DateModified
    $conn.Close()
} 

The first part of the function tests that the file exists – we’ve seen this before when opening databases. Previous functions have mainly relied on SQL and ADO.NET capability. In this case we are using ADO (the older brother of ADO.NET). We need to create a COM object for the connection and open it using the path to our database. This will fail if we already have the database open!! We will see how to cope with this later. We then need to create a ADO Active X catalog representing the database. From that we can list our tables. This list includes the system and user tables.

Technorati Tags: PowerShell,Access,Office 2010

ext stages are to put in some error handling and decide how we will display the structure of an individual table.

10:25 PM | Blog it | PowerShell V2

Comments

Richard Siddaway has turned off comments on this page.

Trackbacks

The trackback URL for this entry is:

http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!2742.trak

Weblogs that reference this entry

None