PowerShell Script to Update MS Access (.MBD) Database
[code language=”powershell”]
###########################################################################
#
# NAME: Update-MSAccessDB-Via-UserDSN
#
# AUTHOR: Govardhan Gunnala
#
# COMMENT:
# 1. Updates MDB file records via "MS Access Database" User DSN
# 2. Creates "MS Access Database" USER DSN if not already exists
# 3. Sets the "MS Access Database" USER DSN to custom .MDB file
# 4. Process all the data Column by Column
# 5. Searches and Replaces a partial string in whole table data
#
#
# VERSION HISTORY: 1.0 7/18/2011 – Initial release
#
###########################################################################
# Import Registry Keys to create a new User DSN named "MS Access Database"
$customDB = ‘C:\Temp\DataSource1.mdb’
REGEDIT /S ‘C:\Temp\USER-DSN-MS-Access-Database.reg’
# Set "MS Access Database" DSN to point to the CustomDB .MDB file location
set-itemproperty ‘HKCU:\Software\ODBC\ODBC.INI\MS Access Database’ -name DBQ -value $customDB
# Search and Repalce strings
$userName = $env:USERNAME
$searchString = "C:\DPW"
$replaceString = "C:\Temp\$userName\DPW"
# Connection Strings: Access DB DSN; Query; Cursor and Lock type
$adOpenStatic = 3
$adLockOptimistic = 3
$connectionString = "DSN=MS Access Database;"
$sourceQuery = "Select * From Connection"
# ADO Objects instantiation
$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset"
# Making ADODB Connection
$objConnection.Open($connectionString)
$objRecordset.Open($sourceQuery, $objConnection, $adOpenStatic, $adLockOptimistic)
# Processing through the query results
$objRecordset.MoveFirst()
While ($objRecordset.EOF -ne $True) {
# Parsing each column and it’s values
ForEach ($column in $objRecordset.get_Fields()) {
$colName = $column.Name
$colvalue = $objRecordset.Fields.Item("$colName").Value
# Matching value to see it contains the search string
if ($colvalue.contains($searchString)) {
# Replacing the search string with repalce string
$value = $objRecordset.Fields.Item("$colName").Value.Replace($searchString, $replaceString)
#commiting to the DB
$objRecordset.Update("$colName",$value)
}
}
$objRecordset.MoveNext()
}
# Close the DB connection handles
$objRecordset.Close()
$objConnection.Close()
[/code]
The Contents of the .Reg File are as below:
[code language=”powershell”]
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI]
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MS Access Database]
"UID"=""
"DBQ"=""
"SafeTransactions"=dword:00000000
"DriverId"=dword:00000019
"Driver"="C:\\PROGRA~2\\COMMON~1\\MICROS~1\\OFFICE14\\ACEODBC.DLL"
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MS Access Database\Engines]
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MS Access Database\Engines\Jet]
"UserCommitSync"="Yes"
"Threads"=dword:00000003
"ImplicitCommitSync"=""
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"MS Access Database"="Microsoft Access Driver (*.mdb, *.accdb)"
[/code]
