re: Hey, Scripting Guy! How Can I Add a Record to a Database Using Windows PowerShell?

Evgeny Tarasov — Thu, 09 Jun 2011 12:26:11 GMT

Hi,

Cool :)

Can you answer me, how I can check information before info was added in Access database?

I have Access database from one table. Fields: Username,Hostname,Data,time

I have script. This script finding info on PC - who now login in PC.

How I can add next element in my script:

If this PC exist in Access database -> update info

If this PC noexist in Access database -> add new string in database

Evgeny

Code:

$PCs = Get-QADComputer -Name *

$path = "C:\FSO\Test2.accdb"

$adOpenStatic = 3

$adLockOptimistic = 3

$objCon = New-Object -com "ADODB.Connection"

$objRec = New-Object -com "ADODB.Recordset"

$objCon.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $path")

$objRec.Open("Select * From Host", $objCon,$adOpenStatic,$adLockOptimistic)

Foreach($1 in $PCs)

{

$CompName = $1.Name

$ShortUser = Get-WmiObject -class Win32_ComputerSystem -ComputerName $1.Name | ForEach-Object {[regex]::replace($_.UserName,".*\\","")}

$objRec.AddNew()

$objRec.Fields.Item("HostName").Value = "$CompName"

$objRec.Fields.Item("UserName").Value = "$ShortUser"

}

$objRec.Update()

$objRec.Close()

$objCon.Close()

$adOpenStatic = 3

$adLockOptimistic = 3