Solved

SQL Update Excel vba

Posted on 2014-10-27
5
266 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 66

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 66

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

690 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