DataBasePowerShell

PowerShell Script to Update MS Access (.MBD) Database

PowerShell Script to Update MS Access (.MBD) Database

###########################################################################
#
# 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()

The Contents of the .Reg File are as below:

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)"
Advertisements