Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Automation Error Using Excel to Update SQL DB

Posted on 2010-01-09
5
Medium Priority
?
408 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
3 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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