Run SQL Update Query From Excel with Parameter VBA

I've got a basic update query that I run from SQL Server Mgnt Studio and I'd like to incorporate it into Excel so that I can type in the parameter that is needed and run it out of Excel. I've been able to incorporate "Select" SQL queries and replace the parameters in the SQL Statement with "?" to create a parameter that is linked to a cell reference, but the same procedure isn't working for an "Update" Statement.

I researched all over and it says I'll probably need to incorporate a bit of VBA Code in order to do this, but I can't quite figure out how to do this.

Here's the Connection String I have that works when I run the "Select" Query:

DSN=AvXXnte;Description=Avionte;UID=thoXXX.shXXX;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=MAU832N;DATABASE=AvXXnte

Open in new window


The Command Text I would like to run is:

UPDATE       TimeEntryBatchError
SET                Approve = 1
FROM            TimeEntryBatch INNER JOIN
                         TimeEntryBatchError ON TimeEntryBatch.TimeEntryBatchGUID = TimeEntryBatchError.TimeEntryBatchGUID
WHERE        (TimeEntryBatch.BatchID = ?)

Open in new window


The "?" is normally the Parameter that is pulling the number from Cell "=Sheet1!$C$2"

Please let me know if anyone has any questions or if I can provide any other detail on how best to accomplish this task.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this code...

Sub getconnected()
    Dim usr As String
    Dim pwd As String
    Dim servername As String
    Dim cn As New ADODB.Connection
    Dim xupdate As String
    servername = "AvXXnte"

    If cn.State = 0 Then
        With cn
            .ConnectionString = "Provider=SQLOLEDB; " & _
                                "Data Source=" & servername & "; " & _
                                "Initial Catalog=AvXXnte;" & _
            .CommandTimeout = 0
        End With
    End If

    xupdate = " UPDATE TimeEntryBatchError " & _
            " SET Approve = 1 " & _
            " FROM TimeEntryBatch INNER JOIN " & _
            " TimeEntryBatchError ON TimeEntryBatch.TimeEntryBatchGUID = TimeEntryBatchError.TimeEntryBatchGUID " & _
            " WHERE (TimeEntryBatch.BatchID ='" & Sheets("Sheet1").Range("C2").Value & "') "

  cn.Execute xupdate


    Set cn = Nothing

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomas-sherrouseAuthor Commented:
I'm guessing I don't have the Driver needed installed to create a connection using ADODB.Connection.

I'm getting an error of:

Complie error:

User-define type not defined

"cn as New ADODB.Connection" is highlighted as the error.

I'll do some research on the solution but if there is an easy one please let me know .

thomas-sherrouseAuthor Commented:
I was able to figure it out.

I replaced:

Dim cn As New ADODB.Connection

Open in new window


Dim Cn
Set Cn = createObject("ADODB.Connection")

Open in new window

Thanks for your help!
thomas-sherrouseAuthor Commented:
I had a small error in the solution provided and I was able to troubleshoot and provide a solution to the issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.