Monitoring Tool on Powershell that queries access

Question

In theory I feel this should kinda be simple. Basically I need a query in access to be run every hour or so through powershell, and then if a certain condition in the query is met I need an email sent.

The email, and the query is simple enough, but I don't know how I can get powershell to query access, and I'm unsure if I can get it the query to run every hour. But if I can just get powershell to run a query on access I'll be happy enough.

You can utilize any OLE or ODBC connection that will talk with Access to connect and make the query. This might be of some help. Powershell to read from database using ODBC;DNS instead of connectionstring — jradich1234
– jradich1234, Commented Dec 9, 2015 at 17:02

Gord Thompson · Accepted Answer · 2015-12-09 18:53:41Z

2

if I can just get powershell to run a query on access I'll be happy enough

The "ACE.psm1" module referenced in the other answer looks useful, but if you prefer to "roll your own" code for database access you could use something like this:

$connStr = @"
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\Public\Database1.accdb
"@
$con = New-Object System.Data.OleDb.OleDbConnection $connStr
$con.Open()
$cmd = New-Object System.Data.OleDb.OleDbCommand "SELECT * FROM Clients", $con
$rdr = $cmd.ExecuteReader()
while ($rdr.Read())
{
    Write ("{0}, {1}" -f $rdr["LastName"], $rdr["FirstName"])
}
$rdr.Close()
$con.Close()

answered Dec 9, 2015 at 18:53

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaqueline Vanek · Accepted Answer · 2015-12-09 17:17:36Z

1

running some scriptblock hourly:

$command =  {

    $trigger = New-JobTrigger -RepetitionInterval (New-TimeSpan -Minutes 60) -RepeatIndefinitely `
    -At (get-date) -Once

    $job = Register-ScheduledJob -Name 'Test' -Trigger $trigger -ScriptBlock {
        (Get-Date).DateTime | Out-File D:\test.txt -Append
    } 
}
Start-Process -FilePath powershell.exe -ArgumentList "-noprofile -command $Command" -Verb runas

have a look the ACE module here:

Use ACE Drivers and PowerShell to Talk to Access and Excel

edited Dec 9, 2015 at 17:17

answered Dec 9, 2015 at 17:03

Jaqueline Vanek

1,13310 silver badges22 bronze badges

Collectives™ on Stack Overflow

Monitoring Tool on Powershell that queries access

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related