
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.
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
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:
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.
Related Access Database
- Запрос к mdb в powershell