PowerShell and MS Access database

Question

We would like to create AD users with PowerShell. With CSV it's not a problem, it's easy with scripts. Next level we would like to create AD users with PowerShell and MS Access database. Now we have the following problem: we can read the Access database, we load it in an object, but when we start our script, it says it is a object and not a string.

So when we convert the object into a string, it loads all lines in the string and it creates one user with all names.

The PowerShell script is:

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT * FROM Users "
$ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$DatabaseName"
$Connection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString
$Command  = New-Object System.Data.OleDb.OleDbCommand $Query, $Connection
$Connection.Open()
$Adapter = New-Object System.Data.OleDb.OleDbDataAdapter $Command
$Dataset = New-Object System.Data.DataSet
[void] $Adapter.Fill($DataSet)
$Connection.Close()
$x = Dataset.Tables
foreach ($u in $x) {
    New-ADUser -Name $u.name ...
}

This is the error in the PowerShell:

Cannot convert 'System Obejct[]' to the type 'System.String' required by Parameter 'String'.

We can convert with the lines

$Name = [string]u.name
New-ADUser -Name $Name ...

When we have 10 Users to add, it adds one user with the name from the ten. We need help to read and convert a single line from the Access database with PowerShell.

Cannell

Answer 1

A dataset contains a list of tables, so you're iterating over the tables in the dataset (even if it's just one table) when you need to iterate over the rows in the table(s).

Change this:

$x = Dataset.Tables
foreach ($u in $x) {
    New-ADUser -Name $u.name ...
}

into this:

$x = Dataset.Tables[0]
foreach ($u in $x) {
    New-ADUser -Name $u.name ...
}

and the problem should disappear.

Parthenogenesis

It's to easy to find this :-D – Cannell

Answer 2

Thanks for the above starting point! Since I'm new, I'm just testing with one table in MS Access with 7 records of users.

ID  User
1   Joe
2   Bob
3   Ray
4   Tom
5   Bill
6   Brian
7   Sam

I've modified the above script to be like this:

#Get the db
$DatabaseName = "c:\scripts\Test1.accdb"
#Sql
$Query = "SELECT * FROM Users"
#Specify the connection string and type
$ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=$DatabaseName"
#Create the new object Connection String
$Connection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString
#Create the command we are sending to the db
$Command  = New-Object System.Data.OleDb.OleDbCommand $Query, $Connection
#Open the connection
$Connection.Open()
#Looks like it's sending the command to the DB here
$Adapter = New-Object System.Data.OleDb.OleDbDataAdapter $Command
#Looks to be building the dataset
$Dataset = New-Object System.Data.DataSet
#Not sure what this does, but looks to use what the $DateSet contains and send it through to be used by the adapter
[void] $Adapter.Fill($DataSet)

#Gets the tables in the dataset, and starts at column 0.
$datasetTables = $Dataset.Tables[0]
#Loop through each record in the table
foreach ($record in $datasetTables) {
    #Writes the value of Field1, in this case the users name
    write-host $record.User
}
#close connection
$Connection.Close()

Which returns:

Joe
Bob
Ray
Tom
Bill
Brian
Sam

Thanks to all in the thread that got me here!

Illuminant