Powershell Query MS Access Database in Microsoft Windows Application

Powershell Access Database – Ms. Powershell is a task automation and configuration management framework from Microsoft. To do this, first you will need ADO (ActiveX Data Objects) Database Connection to connect the Access database to PowerShell. Mostly, it brought along when you install Windows, but if you don’t have it you can download it for free.

Powershell Access Database Application

Powershell Access Database Application



To get started, open Windows Powershell application. It should be brought along when you installing windows. Then, follow these steps:
1. Script for Powershell Access Database started by assigning the path to access database file:
$path = “[YourPathHere]YourDatabase.mdb”
$adOpenStatic = 3
$adLockOptimistic = 3

2. Next, create the object for connection and recordset which used for connect to the database itself and the records inside:
$conn = new-object -com ADODB.Connection
$rs = new-object -com ADODB.Recordset

3. Set up the connection provider to connect with the database.
For Access 2007 and earlier:
$conn.Open(“Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $path”)
For Access 2010 and later:
$conn.Open(“Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $path”)

4. Select the records using SQL statement:
$rs.Open(“SELECT ColumnA, ColumnB, ColumnC
FROM SampleTable”
WHERE ColumnC < 6
ORDER BY ColumnA ASC,
$conn, $adOpenStatic, $adLockOptimistic)
Note: the code above must be in one line. We break it into several line for tutorial purpose.

5. After you move the RecordSet, it will display the Fields property:
$rs.Fields.Item(“ColumnA”).value
$rs.Fields.Item(“ColumnB”).value
$rs.Fields.Item(“ColumnC”).value

Read Also:  Ms Access Query Examples on Expression Syntax Functions

6. If you want to add new record, you can add the value from the code above:
$rs.AddNew()
$rs.Fields.Item (“ColumnA”).value = “ABC01”
$rs.Fields.Item (“ColumnB”).value = ”Ketchup”
$rs.Fields.Item (“ColumnC”).value = 3
$rs.Update()

7. Lastly, close the connection and recordset:
$conn.Close
$rs.Close

You can see the full code here:
$path = “[YourPathHere]YourDatabase.mdb”
$adOpenStatic = 3
$adLockOptimistic = 3
$conn = new-object -com ADODB.Connection
$rs = new-object -com ADODB.Recordset
$conn.Open(“Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $path”)
$rs.Open(“SELECT ColumnA, ColumnB, ColumnC FROM SampleTable” WHERE ColumnC < 6 ORDER BY ColumnA ASC, $conn, $adOpenStatic, $adLockOptimistic)
$rs.Fields.Item(“ColumnA”).value
$rs.Fields.Item(“ColumnB”).value
$rs.Fields.Item(“ColumnC”).value
$conn.Close
$rs.Close

Example:

Powershell Query MS Access

Powershell Query MS Access



Aside the method above, there are still other type of code. You can even define your own. However, you still need to follow the basic provided in each steps.



Access Database Tags: #powershell access database #powershell access database query #powershell query access database accdb #powershell query ms access

Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to
MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access


Must read×

Top