title: operation not allowed when object is closed "SQL connection giving error with VBA"
body: <p>I am facing an issue while running a long SQL query with VBA, but when I run a simple one-line select query it works fine BUT</p>
  <p>when I run the below complex query it fails, also I'd like to input some values from a defined range/cell into sql query like I would like to input L1100 as timezone from cell A2, what format should i use,  any suggestions on this would be much appreciated</p>
  <pre class="lang-vb prettyprint-override"><code>Public DBConn As ADODB.Connection
  
  Public Sub createconn()
  
  Dim UserId, Password As String
  Set DBConn = New ADODB.Connection
  Server = &quot;XXXX&quot;
  UserId = &quot;XXXXX&quot;
  Password = &quot;XXXX&quot;
  With DBConn
      .CommandTimeout = 30
      .Open &quot;PROVIDER=MSDASQL;&quot; &amp; _
      &quot;DRIVER={Microsoft ODBC for Oracle};&quot; &amp; _
      &quot;SERVER=&quot; &amp; Server &amp; &quot;;&quot; &amp; _
      &quot;UID=&quot; &amp; UserId &amp; &quot;;PWD=&quot; &amp; Password &amp; &quot;;&quot;
  End With
  
  End Sub
  
  Sub Stalecheck()
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Dim DBresults As ADODB.Recordset
  Dim sqlquery As String, wsData As Worksheet, Datasht As Worksheet
  Dim elr As Long
  Dim lRow As Long
  Set DBresults = New ADODB.Recordset
  
  
  Set wsData = Worksheets(&quot;Sheet1&quot;)
  Set Datasht = ThisWorkbook.Sheets(&quot;Data&quot;)
  'wsData.Range(&quot;U5:Y5&quot;, Range(&quot;U5&quot;).End(xlDown)).Clear
  Datasht.Cells.ClearContents
  'lRow = wsData.Cells(Rows.Count, 14).End(xlUp).Row
  
  'Worksheets(&quot;UPDATER&quot;).Range(&quot;N2&quot;, &quot;N&quot; &amp; lRow).Copy Destination:=Worksheets(&quot;UPDATER&quot;).Range(&quot;V5&quot;)
  'Mainsheet.Range(&quot;B7:B15&quot;).ClearContents
  If wsData.Range(&quot;B3&quot;).Value &lt;&gt; &quot;&quot; Then
  
  
      Call createconn
      
      'sqlquery = &quot;select * from instrument_equity&quot;
  
      
    sqlquery = &quot;select i.name, i.pkey, nvl(i.enddate,round((i.periodyears * 365.25),0) + vyc.asof) maturity,&quot; &amp; vbNewLine &amp; _
  &quot;nvl(i.period,i.enddate) period, vyc.asof, vyc.timezone, vyc.snaptime, vyc.rowkey, nvl(nvl(pfx.price, p.rate*100),vp.rate) rate,&quot; &amp; vbNewLine &amp; _
  &quot;nvl(nvl(pfx.updatetime, p.snaptime),vp.updatetime) updatetime, vms.symbol source&quot; &amp; vbNewLine &amp; _
  &quot;from val_yield_curves vyc&quot; &amp; vbNewLine &amp; _
  &quot;JOIN val_yc_def_elements el ON el.pkey=valuations_yieldcurves.getCurveDefinitionKey(vyc.asof, vyc.object_id)&quot; &amp; vbNewLine &amp; _
  &quot;JOIN instruments i on i.pkey = el.instrument&quot; &amp; vbNewLine &amp; _
  &quot;LEFT JOIN (SELECT asof, curve, instrument, rate*100 rate, quality, source, updatetime, bid, ask FROM val_prices_interestrates&quot; &amp; vbNewLine &amp; _
  &quot;UNION SELECT asof, curve, instrument, rate*100, quality, source, updatetime, bid, ask FROM val_prices_basisswaps&quot; &amp; vbNewLine &amp; _
  &quot;UNION SELECT asof, curve, instrument, rate*100, quality, source, updatetime, bid, ask FROM val_prices_oisrates&quot; &amp; vbNewLine &amp; _
  &quot;UNION SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask FROM val_prices_fx)&quot; &amp; vbNewLine &amp; _
  &quot;vp ON i.pkey = vp.instrument&quot; &amp; vbNewLine &amp; _
  &quot;and vp.asof=vyc.asof&quot; &amp; vbNewLine &amp; _
  &quot;and vp.curve=vyc.rowkey&quot; &amp; vbNewLine &amp; _
  &quot;LEFT JOIN prices_fixings p ON p.instrument = i.pkey and p.asof = vyc.asof&quot; &amp; vbNewLine &amp; _
  &quot;LEFT JOIN val_prices_fxfixings pfx ON pfx.instrument = i.pkey&quot; &amp; vbNewLine &amp; _
  &quot;and pfx.asof = vyc.asof&quot; &amp; vbNewLine &amp; _
  &quot;and pfx.timezone = vyc.timezone&quot; &amp; vbNewLine &amp; _
  &quot;LEFT JOIN val_mds_sources vms ON vms.pkey = vp.source&quot; &amp; vbNewLine &amp; _
  &quot;WHERE vyc.asof = '11Jul20'&quot; &amp; vbNewLine &amp; _
  &quot;and vyc.timezone = 'L1000'&quot; &amp; vbNewLine &amp; _
  &quot;and vyc.object_id like 'CAD_IRIndex_3M'&quot; &amp; vbNewLine &amp; _
  &quot;and i.type &lt;&gt; 'Future'&quot; &amp; vbNewLine &amp; _
  &quot;ORDER BY vyc.object_id, i.type desc, el.type, i.ccy, i.ccy2, nvl(i.periodyears, nvl((i.enddate-trunc(sysdate))/365,0)), vyc.snaptime DESC&quot;
  
  
  
  
  
      'sqlquery = Left(sqlquery, Len(sqlquery) - 1) &amp; &quot;)&quot;
      DBresults.Open sqlquery, DBConn, adOpenDynamic
      If Not DBresults.EOF Then Datasht.Range(&quot;A1&quot;).CopyFromRecordset DBresults
          
      
      End If
  
  
  Set DBresults = Nothing
  Call close_conn
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
  End Sub
  
  
  
  Sub close_conn()
  
  DBConn.Close
  End Sub
  </code></pre>