Tuesday, March 25, 2014

Access And Powershell: How to populate a table

Here's the code:

function GetValue
{
    param
    (
    [string]$Name,
    [object]$obj
    )

    [string]$PName = $Name + " = "
    [string]$tempstr = $obj.GetObjectText_(0)

    $pos = $tempstr.IndexOf($PName)
    if ($pos -gt 0)
    {
        $pos = $pos + $PName.Length
        $tempstr = $tempstr.SubString($pos, ($tempstr.Length - $pos))
        $pos = $tempstr.IndexOf(";")
        $tempstr = $tempstr.SubString(0, $pos)
        $tempstr = $tempstr.Replace('"', "")
        $tempstr = $tempstr.Replace("}", "")
        $tempstr = $tempstr.Replace("{", "")
        $tempstr = $tempstr.Trim()
        if($tempstr.Length -gt 14)
        {
            if($obj.Properties_.Item($Name).CIMType -eq 101)
            {
                [System.String]$tstr = $tempstr.SubString(4, 2)
                $tstr = $tstr + "/"
                $tstr = $tstr + $tempstr.SubString(6, 2)
                $tstr = $tstr + "/"
                $tstr = $tstr + $tempstr.SubString(0, 4)
                $tstr = $tstr + " "
                $tstr = $tstr + $tempstr.SubString(8, 2)
                $tstr = $tstr + ":"
                $tstr = $tstr + $tempstr.SubString(10, 2)
                $tstr = $tstr + ":"
                $tstr = $tstr + $tempstr.SubString(12, 2)
                $tempstr = $tstr
            }
        }
        return $tempstr
    }
    else
    {
        return ""
    }
}

param
(
    Manditory=$True
    $Databasename
)

    $l = new-object -comobject Wbemscripting.SWbemLocator
    $svc = $l.ConnectServer(".", "root\cimv2")
    $svc.Security_.AuthenticationLevel=6
    $svc.Security_.ImpersonationLevel=3
    $ob = $svc.Get("Win32_Process")

    $oaccess = new-object -comobject Access.Application
    $oaccess.CreateCurrentDatabase($DatabaseName, 9)

    $db = $oAccess.CurrentDB()
    $tbldef = $db.CreateTableDef("Processes_Properties")

    foreach($prop in $ob.Properties_)
    {
        $fld = $tbldef.CreateField($prop.Name, 12)
        $fld.AllowZeroLength = $true
        $tbldef.Fields.Append($fld)
    }
    $db.TableDefs.Append($tbldef)
   
    $objs = $ob.Instances_(0)

    $rs = $db.OpenRecordset("Select * From Processes_Properties", Exclusive:=False)
    foreach($obj in $objs)
    {
        $rs.AddNew()
        foreach($prop in $obj.Properties_)
        {
            $rs.Fields($prop.Name).Value = GetValue $prop.Name $obj
        }
        $rs.Update()
    }

No comments:

Post a Comment