Update SQL Server table from Excel VBA

Question

I'm trying to use the below code to take the active cell and update a table in SQL Server.

Sub UpdateTable()
Dim rngName As Range
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=MyServer; " & _
            "Initial Catalog=Mydb;" & _
            "User ID=User;" & _
            "Password=Pwd;" & _
            "Trusted_Connection=No"
Set rngName = ActiveCell
'Debug.Print (rngName)
 Set cnn = New ADODB.Connection
Application.ScreenUpdating = False
cnn.Open cnnstr
Set rs = New ADODB.Recordset
uSQL = "UPDATE MyTable SET FieldNameX = 1 WHERE FieldNameY = '" & rngName & "' "
rs.CursorLocation = adUseClient
rs.Open uSQL, cnn, adOpenStatic, adLockOptimistic, adCmdText
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

When stepping through the code, it run time errors on the line rs.close, says Operation is not allowed when the object is closed I've set and opened the record set in the code so why would it be closed?

What would I need to do to correct the issue and let the active cell populate the query and update the table in SQL Server?

can you confirm that the recordset rs is successfully opened? Try ?rs.recordcount in the Immediate Window (use CTRL_G). If the error is happening on the line 'rs.Open' then the conenction is probably not open. — Our Man in Bananas
– Our Man in Bananas, Commented Mar 6, 2014 at 16:38
Where should I put the line rs.recordcount? I placed it after my line rs.open, and it run time errors with Wrong number of arguments or invalid property assignment — Jez
– Jez, Commented Mar 6, 2014 at 16:43
Your update SQL doesn't return any records, so you'd expect the recordset to be empty. For updates you're much better off using the Connection.Execute approach - there is a parameter RecordsAffected you can use to determine how many rows were affected by your update. msdn.microsoft.com/en-us/library/windows/desktop/… — Tim Williams
– Tim Williams, Commented Mar 6, 2014 at 16:51
Why do you say my Update SQL doesn't return any records, when I do Debug.Print(uSQL) in the Immediate window it posts the SQL, I've tested this in SSMS and it will update the records. There will always just be one record to update and that would be the active cell in excel. I import the data from SQL Server and the Update query marks that records as downloaded from the table. — Jez
– Jez, Commented Mar 6, 2014 at 16:58
Your update doesn't return any records: all it does is update the table. That doesn't cause any records to be selected and populate your recordset. — Tim Williams
– Tim Williams, Commented Mar 6, 2014 at 19:07

Jez · Accepted Answer · 2014-03-06 17:23:14Z

This below is the code I used to be able to update the table in SQL Server, this works just how I wanted. It takes the activecell and updates.

Sub UpdateTable()
Dim cnn As ADODB.Connection
Dim uSQL As String
Dim rngName As Range
Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=MyServer; " & _
            "Initial Catalog=Mydb;" & _
            "User ID=User;" & _
            "Password=Pwd;" & _
            "Trusted_Connection=No"
    Set rngName = ActiveCell
cnn.Open cnnstr
uSQL = "UPDATE MyTable SET FieldNameX = 1 WHERE FieldNameY= '" & rngName & "' "
'Debug.Print (uSQL)
cnn.Execute uSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Collectives™ on Stack Overflow

Update SQL Server table from Excel VBA

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related