Access Database

[sekou2331]

Hi,

I have a Ms Access DB that I want to read from and insert in to. I need to insert an array of objects into DB. I was able to run the code below to read certain Fields but is not all the data in the Field. I need to be able to read everything from the feilds and insert into all the Fields.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.ps1)

1. $adOpenStatic = 3
2. $adLockOptimistic = 3
3.  
4. $conn=New-Object -com "ADODB.Connection"
5. $rs = New-Object -com "ADODB.Recordset"
6. $conn.Open('Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\DB1.accdb;Persist Security Info=True;')
7.  
8. $rs.Open("SELECT * FROM table1",$conn,$adOpenStatic,$adLockOptimistic)
9.  
10. $rs.Fields.Item("Name").value
11. $rs.Fields.Item("OperatingSystem").value
12. $rs.Fields.Item("Model").value
13. $rs.Fields.Item("SerialNumber").value
14.  
15. $conn.Close
16. $rs.Close

[jvierra]

Use the Net classes for data and not the old COM classes. The Net classes are much easier and more powerful.

You also need to post the error. If there are nulls in any fields then the field will return a truncated value.

[jvierra]

Here is how to use the ACE drivers with PowerShell and the Net data classes:

Code: Select all

# create a connection and open it
$filename='C:\DB1.accdb'
$connString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$filename"
$conn=New-Object System.Data.OleDb.OleDbConnection($connString)
$conn.Open()

# create a command object and execute its reader.
$cmd = $conn.CreateCommand()
$cmd.CommandText = 'SELECT * FROM table1'
$rdr = $cmd.ExecuteReader()

# load the data table object
$dt = New-Object System.Data.DataTable
$dt.Load($rdr)
$dt

[sekou2331]

Thanks. I can now read but I am trying to write and I am getting this error.

ERROR: Error formatting a string: Index (zero based) must be greater than or equal to zero and less than the size of the argument list..
PowershellAddDB.ps1 (93, 3): ERROR: At Line: 93 char: 3
ERROR: +         $sql = "INSERT INTO table(User_Name,IP_Address,Computer ...
ERROR: +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (INSERT INTO Ins...}','{7}','{8}'):String) [], RuntimeException
ERROR:     + FullyQualifiedErrorId : FormatError
ERROR:
ERROR: Exception calling "ExecuteNonQuery" with "0" argument(s): "Command text was not set for the command object."
PowershellAddDB.ps1 (98, 3): ERROR: At Line: 98 char: 3
ERROR: +         $writecmd.executenonquery() | out-null
ERROR: +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
ERROR:     + FullyQualifiedErrorId : OleDbException
ERROR:
ERROR: Error formatting a string: Index (zero based) must be greater than or equal to zero and less than the size of the argument list..

Code: Select all

$InfoParse | ForEach-Object{
		$connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB1.accdb;Persist Security Info=True;"
		#open a connection 
		[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null
		$writeconn = New-Object System.Data.OleDb.OleDbConnection
		$writeconn.connectionstring = $connstr
		$writeconn.open()
		
		
		# Create a  command
		$sql = "INSERT INTO table(User_Name,IP_Address,Computer_Name,City,Operating_System,Software_Name,Software_Version,LastBootTime)
				VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')" -f $_.User_Name, $_.IP_Address. $_.Computer_Name, $_.City, $_.Operating_System, $_.Software_Name, $_.Software_Version, $_.LastBootTime
		$writecmd = New-Object system.Data.OleDb.OleDbCommand
		$writecmd.connection = $writeconn
		$writecmd.commandtext = $sql
		$writecmd.executenonquery() | out-null
	}
	$writeconn.close()
}

[jvierra]

This is unnecessary: "[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null"

That type is part of PowerShell.

[jvierra]

If you arbitrarily edit the code I posted without understanding it you will end up with a mess. Try to understand what the code is doing and why.

Code: Select all

# create a connection and open it
$filename='C:\DB1.accdb'
$connString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$filename"
$conn=New-Object System.Data.OleDb.OleDbConnection($connString)
$conn.Open()

# create a command object and execute its reader.
$cmd = $conn.CreateCommand()
$cmd.CommandText = 'SELECT * FROM table1'
$rdr = $cmd.ExecuteReader()

# load the data table object
$dt = New-Object System.Data.DataTable
$dt.Load($rdr)
$dt

$connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB1.accdb;Persist Security Info=True;"
$conn = New-Object System.Data.OleDb.OleDbConnection($connstr)
$conn.open()
$cmd = $conn.CreateCommand()
$cmd.commandtext = $sql
$sqltmplt =@'
INSERT INTO table(
    User_Name,
    IP_Address,
    Computer_Name,
    City,
    Operating_System,
    Software_Name,
    Software_Version,
    LastBootTime
)
VALUES(
    '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'
)
'@

$InfoParse | 
    ForEach-Object{
		$sql = $sqltmplt -f $_.User_Name, $_.IP_Address. $_.Computer_Name, $_.City, $_.Operating_System, $_.Software_Name, $_.Software_Version, $_.LastBootTime
        $cmd.CommandText = $sql
		$records = $cmd.ExecuteNonQuery()
        Write-Host "Posted $records" -Fore Green
	}

$conn.close()

[jvierra]

You must also take the time to learn to read the error messages. This error message points directly at the first error that you had.

ERROR: Error formatting a string: Index (zero based) must be greater than or equal to zero and less than the size of the argument list..
PowershellAddDB.ps1 (93, 3): ERROR: At Line: 93 char: 3
ERROR: + $sql = "INSERT INTO table(User_Name,IP_Address,Computer ...