Solved

SQL Update Excel vba

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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