title: vba get value from sql server recordset
body: <p>My code to retrieve value is as below:</p>
  
  <pre><code>Sub UploadData()
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  
  Dim cn As ADODB.Connection
  Set cn = New ADODB.Connection
  
  Dim strConn As String
  Dim sql As String
  
  strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=.\sql2000;INITIAL CATALOG=EquityDB;INTEGRATED    SECURITY=sspi;"
  
  cn.Open strConn
  
  sql = "select * from  EquityDB.dbo.table1 where field1 = '" &amp; Replace(Range("d1").Value, "'", "''") &amp; "'"
  
   rs.Open sql, cn, adOpenDynamic, adLockOptimistic
    GetData = rs.Fields(0).Value
  
  
   If Not GetData = "" Then
  
   cn.Execute sql001
  
   Else
  
   cn.Execute sql002
  
  End If
  </code></pre>
  
  <p>sql001 is an insert, and sql002 is an update</p>
  
  <p>When I run the macro, I got error saying operation is not allowed when the record is open for the line</p>
  
  <pre><code>    rs.Open sql, cn, adOpenDynamic, adLockOptimistic
  </code></pre>
  
  <p>If I change </p>
  
  <pre><code>   If Not GetData = "" Then
  </code></pre>
  
  <p>to </p>
  
  <pre><code>   If Not GetData Is Null Then
  </code></pre>
  
  <p>I get error saying "object required" with the line</p>
  
  <pre><code>   If Not GetData Is Null Then
  </code></pre>
  
  <p>Any advice on how to fix the bug would be great!</p>