Categories

Testing Connection to Access database

Many of the functions we have created so far have taken a connection to an Access database as a parameter.  At the time we pass in the connection we don’t actually know if the connection is open. Test-AccessConnection can be used to test the connection before we use it.  All we do is test the value of the state property on the connection object.  if it is Open we return true other wise false.  Only values at present are open and closed.

001
002
003
004
005
006
007
008
function Test-AccessConnection {
param (
    [System.Data.OleDb.OleDbConnection]$connection
)  
    if ($connection.State -eq "Open"){$open = $true}
    else {$open = $false}
    $open   
}

 

We can use the Test-AccessConnection function like this

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> if (Test-AccessConnection $db){Get-AccessData -sql "select * from test1" -connection $db}

FirstName                            LastName                           DOB
---------                            --------                           ---
Fred                             ... Smith                          ... 01/09/1979 00:00:00
John                             ... Smith                          ... 01/07/1981 00:00:00
James                            ... Jones                          ... 18/04/1981 00:00:00

PS> Close-AccessDatabase $db
PS> if (Test-AccessConnection $db){Get-AccessData -sql "select * from test1" -connection $db}else{Write-Host "Database Closed"}
Database Closed
PS>

Use the Test-AccessConnection function in an if statement. If $true is returned we can execute our command.  This could be extended to give us a message if the connection is closed.

Leave a Reply