?
Solved

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

Posted on 2013-12-16
1
Medium Priority
?
962 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 2000 total points
ID: 39721154
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

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Welcome to 2018! Exciting things lie ahead in the world of tech. To start things off, we compiled great member articles on how to stay safe, ways to learn, and much more! Read on to start your new year right.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Screencast - Getting to Know the Pipeline
Suggested Courses

615 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