Solved

SQL Update Excel vba

Posted on 2014-10-27
5
260 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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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