Powershell query Access database
[system.reflection.assembly]::LoadWithPartialName("System.Data")
Third, create a Connection. Now, a connection can only be used for one operation at a time - reading and making changes are two different things, so if you want to be reading and writing data all at once, you'll need two connections (one to read from, and the other to issue changes/additions/whatever). There are two kinds of connection you can make: One to SQL Server, and one to "anything else." The SQL Server one looks like this:
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Your connection string here"
$connection.Open
If you need another kind of database, use a System.Data.OleDb.OleDbConnection instead. That pattern will persist for the remaining examples: I'll use the OleDb syntax, but you can substitute in the SQL Server ones if you prefer.
With an open connection, you can build a command. You're going to need to issue a SQL language query, and that's a bit beyond the scope of this article. There's a free language tutorial here, and I also did a video training series on the language. So, assuming you have a query ready to go, you build the command like this:$command = New-Object System.Data.OleDb.OleDbCommand
$command.Connection = $connection
$command.CommandText = "Your query here"
Keep in mind that if you're opening multiple connections, you'll need each one in its own variable ($connection2, $connection3, etc), and each will also need a unique command ($command2, etc).
What you do next depends on the query you issued. Action queries - INSERT, UPDATE, and DELETE queries - are executed like this:
$command.ExecuteNonQuery
And that's it - you're done. SELECT queries, which return data, look something like this:
$reader = $command.ExecuteReader
while ($reader.read) {
$reader.GetValue(0) # gets the value for the first column in the query