locked
Help adding to a accdb using powershell RRS feed

  • Question

  • I have been learning how to work with an access database and Im running into issues adding a record to a table called Computers.

    $adOpenStatic = 3
    $adLockOptimistic = 3
    $objConnection = New-Object -com "ADODB.Connection"
    $objRecordSet = New-Object -com "ADODB.Recordset"
    $objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Temp\Database1.accdb")
    $objRecordset.Open("Select * From Computers", $objConnection, $adOpenStatic, $adLockOptimistic) 
    $objRecordSet.AddNew()
    $objRecordSet.Fields.Item("Computer Name").Value = "Test Computer"
    $objRecordSet.Update()
    $objRecordSet.Close()
    $objConnection.Close()

    When I run it I get the below errors. can someone help and show me what Im doing wrong.

    ERROR: Exception calling "Open" with "1" argument(s): "Provider cannot be found. It may not be properly installed."
    database_Connection.ps1 (16): ERROR: At Line: 16 char: 20
    ERROR: + $objConnection.Open <<<< ("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Temp\Database1.accdb")
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:
    ERROR: Exception calling "Open" with "4" argument(s): "The connection cannot be used to perform this operation. It is either closed or invalid in this context."
    database_Connection.ps1 (17): ERROR: At Line: 17 char: 19
    ERROR: + $objRecordset.Open <<<< ("Select * From Open_Vuln", $objConnection, $adOpenStatic, $adLockOptimistic)
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:
    ERROR: Exception calling "AddNew" with "0" argument(s): "Operation is not allowed when the object is closed."
    database_Connection.ps1 (19): ERROR: At Line: 19 char: 21
    ERROR: + $objRecordSet.AddNew <<<< ()
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:
    ERROR: Exception getting "Item": "Item cannot be found in the collection corresponding to the requested name or ordinal."
    database_Connection.ps1 (20): ERROR: At Line: 20 char: 26
    ERROR: + $objRecordSet.Fields.Item <<<< ("Computer Name").Value = "Test Computer"
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
    ERROR:     + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI
    ERROR:
    ERROR: Exception calling "Update" with "0" argument(s): "Operation is not allowed when the object is closed."
    database_Connection.ps1 (21): ERROR: At Line: 21 char: 21
    ERROR: + $objRecordSet.Update <<<< ()
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:
    ERROR: Exception calling "Close" with "0" argument(s): "Operation is not allowed when the object is closed."
    database_Connection.ps1 (23): ERROR: At Line: 23 char: 20
    ERROR: + $objRecordSet.Close <<<< ()
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:
    ERROR: Exception calling "Close" with "0" argument(s): "Operation is not allowed when the object is closed."
    database_Connection.ps1 (24): ERROR: At Line: 24 char: 21
    ERROR: + $objConnection.Close <<<< ()
    ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    ERROR:     + FullyQualifiedErrorId : ComMethodTargetInvocation
    ERROR:

    Wednesday, December 18, 2013 2:45 AM

Answers

  • I had to do a little testing.  On 64 bit systems with Access 12 and later there is no driver installed.  THe 32 bit driver is installed so run under the 32 bit version of PowerShell and use this string and command.

    $conn=New-Object -com "ADODB.Connection"
    $conn.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\scripts\test.mdb')

    I would use OleDb and the ADO.Net becsue it will work better.


    ¯\_(ツ)_/¯

    • Marked as answer by Zach Shupp Wednesday, December 18, 2013 3:27 AM
    Wednesday, December 18, 2013 3:18 AM

All replies

  • Read the error it tells you exactly what the problem is.

    ERROR:Exceptioncalling "Open"with"1"argument(s):"Provider cannot be found. It may not be properly installed."


    ¯\_(ツ)_/¯

    Wednesday, December 18, 2013 3:07 AM
  • How can I veryify that its properly installed? I have office 2013.

    Wednesday, December 18, 2013 3:09 AM
  • I had to do a little testing.  On 64 bit systems with Access 12 and later there is no driver installed.  THe 32 bit driver is installed so run under the 32 bit version of PowerShell and use this string and command.

    $conn=New-Object -com "ADODB.Connection"
    $conn.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\scripts\test.mdb')

    I would use OleDb and the ADO.Net becsue it will work better.


    ¯\_(ツ)_/¯

    • Marked as answer by Zach Shupp Wednesday, December 18, 2013 3:27 AM
    Wednesday, December 18, 2013 3:18 AM
  • I didnt use the string you listed but i opened PowerShell in 32bit mode and it worked. Thank you.
    Wednesday, December 18, 2013 3:28 AM