?
Solved

Automation Error Using Excel to Update SQL DB

Posted on 2010-01-09
5
Medium Priority
?
403 Views
Last Modified: 2012-05-08
All,

I am receiving a "Run-time error '-2147217843 (80040e4d)':  Automation Error" when I try to run the attached code.  Basically I am trying to update muliple records without having to do each one individually using Excel connecting to our SQL DB.

Can someone tell me what is wrong with this?  It's the first time I have tried to use a For...Each statement to update something.

I have tried adding the CurrentProject.Connection.Execute strSQL but it gives me the same error.
Sub Asset_Tag_Update()

Set conSQL = New ADODB.Connection
conSQL.Open "Server=OURSQLSERVER;DRIVER=SQL Server;Database=maverick;User ID=mylogin;Password=mypassword"


For Each Cell In Range("rt_ser")
rt_serial = ActiveCell.Value
new_asset_tag = ActiveCell.Offset(0, 1).Value

rt_serial = "'" & rt_serial & "'"
new_asset_tag = "'" & new_asset_tag & "'"

strSQL = "update audit_item ai" & _
" set new_asset_tag = " & new_asset_tag & "" & _
" where rt_serial = " & rt_serial & ""



Next Cell


End Sub

Open in new window

0
Comment
Question by:Brad Sims, CCNA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Expert Comment

by:sanket_1985
ID: 26274992
try after changing

conSQL.Open "Server=OURSQLSERVER;DRIVER=SQL Server;Database=maverick;User ID=mylogin;Password=mypassword"

to

conSQL.Open "Source=OURSQLSERVER;DRIVER=SQL Server;Initial Catalog=maverick;User ID=mylogin;Password=mypassword"

probably it should work.
0
 
LVL 7

Author Comment

by:Brad Sims, CCNA
ID: 26275495
I tried the change but no luck.  I still get the same error.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 26350521
Cell is a reserved word and should not be used as a variable.
Create a cellThing variable.

Force variable declaration.

In your SQL statement, you aliased the table as "ai", but you did not qualify any of the fields with that alias.
Option Explicit

Sub Asset_Tag_Update()
  Dim cellThing As Range
  Dim conSQL As ADODB.Connection

  Set conSQL = New ADODB.Connection
  conSQL.Open "Server=OURSQLSERVER;DRIVER=SQL Server;Database=maverick;User ID=mylogin;Password=mypassword"
  
  For Each cellThing In Range("rt_ser")
    rt_serial = cellThing.Value
    new_asset_tag = cellThing.Offset(0, 1).Value
  
    rt_serial = "'" & rt_serial & "'"
    new_asset_tag = "'" & new_asset_tag & "'"

    strSQL = "update audit_item" & _
        " set new_asset_tag = " & new_asset_tag & _
        " where rt_serial = " & rt_serial
    
    conSQL.Execute strSQL
  Next

End Sub

Open in new window

0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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