Solved

SQL Update Excel vba

Posted on 2014-10-27
5
263 Views
Last Modified: 2014-10-27
Hello,
can you please help,
I'm trying to update an email address.
I don't get any errors, but nothing gets updated.

Sub UPDATE_Drivers_Emails()
Dim sh As Worksheet
Set sh = Sheets("Emails_Driver")

    Dim cnn As New adodb.Connection
    Set cnn = New adodb.Connection
    Dim strSQL As String

  ' sTART
cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=xxxxxxxxx;Initial Catalog=Cccccccccccc;Data Source=Wassim-XPS;"

strSQL = "Update Drivers set Email ='" & sh.[J1].Value & "' where ((DriverNumber = '" & sh.[M1].Value & "'))"
                             
cnn.BeginTrans
cnn.CommitTrans

cnn.Close
Set cnn = Nothing
 MsgBox "Updated"
End Sub
0
Comment
Question by:W.E.B
[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
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40406897
For starters, add the below line right below your strSQL = line.
Msgbox  strSQL     'or Debug.Print strSQL if you're in code

Open in new window

Then write down what is displayed in this question.

At a glance, if DriverNumber is a number then lose the single quote marks ' surrounding the value.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40406906
Also, you are missing the line that actually executes it...

cnn.Execute strSQL
0
 

Author Comment

by:W.E.B
ID: 40406908
Hello,
I dropped the single Quote
I get a message
Update Drivers Set Email = 'xxxxx@xxxxxx.om' where (DriverNumber = 2002)

Nothing gets updated though in SQL,
thanks
0
 

Author Comment

by:W.E.B
ID: 40406913
thank you
this is what I missed.

cnn.Execute strSQL
0
 

Author Closing Comment

by:W.E.B
ID: 40406915
Thank you
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 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