I am working on a project to track profit and loss from a billing standpoint. We have a per piece cost that I am using to track the expenses. The end user would like to be able to change the per piece cost from time to time prior to update the table. I am trying to us a macro to take a value from sheet3 cell B2 in the Excel worksheet in order to update a table in SQL server. I am missing something as when I run the macro it does not update the table. Could anyone tell me what I am missing. This is my first attempt at using VBA so I'm stuck for the moment.
Sub UpdateSQLData()
Dim cnn As ADODB.Connection
Dim MySQL As String
Worksheets("Sheet3").Visible = True
Worksheets("Sheet3").Select
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=database;Data Source=server;UID=uid;PWD=pwd;"
Worksheets("Sheet3").Activate
For i = 2 To Range("B2").Select
MySQL = "UPDATE opi_bill_cost " & _
"SET Cost ='" & Range("B2").Value & "', Outcome = '"
cnn.Execute MySQL
Next i
cnn.Close
Set cnn = Nothing
End Sub
Sub UpdateSQLData()
Dim cnn As ADODB.Connection
Dim MySQL As String
Worksheets("Sheet3").Visible = True
Worksheets("Sheet3").Select
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=database;Data Source=server;UID=uid;PWD=pwd;"
Worksheets("Sheet3").Activate
For i = 2 To Range("B2").Select
MySQL = "UPDATE opi_bill_cost " & _
"SET Cost ='" & Range("B2").Value & "', Outcome = '"
cnn.Execute MySQL
Next i
cnn.Close
Set cnn = Nothing
End Sub