Solved

Trying to do a SQL insert  from powershell to a MSAccess database

Posted on 2013-12-16
1
788 Views
Last Modified: 2014-01-06
Hi,

I am trying to do a SQL insert into MSAccess database and coming up with an error in powershell.

Do u have any suggestions on how I can fix the code below:

    clear
    $db = New-Object -com "ADODB.Connection"
    
    $db.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\ad\VP.accdb")    

    $sql = "insert into vp_debug (Machine_Name) Values ('Test')"
    
    $test_cmd = New-Object System.Data.OleDb.OleDbCommand


    $test_cmd.Connection = $db
    $test_cmd.CommandText = $sql

    $test_cmd.ExecuteNonQuery()

Open in new window


The error I get is:

Exception setting "Connection": "Cannot convert the "System.__ComObject" value of type "System.__ComObject#{00001550-0000-0010-8000-00aa006d2ea4}" to type "System.Data.OleDb.OleDbConnection"."
At C:\ad\Untitled1.ps1:11 char:5
+     $test_cmd.Connection = $db
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Connection property has not been initialized."
At C:\ad\Untitled1.ps1:14 char:5
+     $test_cmd.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
0
Comment
Question by:whorsfall
1 Comment
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Try this - I don't think your connection definition is quite right:

$cn = New-Object data.OleDb.OleDbConnection "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\ad\VP.accdb"
$sql = "insert into vp_debug (Machine_Name) Values ('Test')"
    
$testcmd = New-Object System.Data.OleDb.OleDbCommand $sql
$testcmd.connection = $cn
$testcmd.ExecuteNonQuery()

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will help you understand what HashTables are and how to use them in PowerShell.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now