title: Connect to Oracle DB using VBA using Macro References
body: <p>I have a macro in VBA in which I connect to an Oracle DB.</p>
  <pre><code>Sub Only_Active_Hierarchy()
  
  Dim rs As ADODB.Recordset, r As Range, Query As String, c As New ADODB.Connection
  
  Set c = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  c.ConnectionString = &quot;Driver={Oracle in OraClient19Home1};DBQ=DBNAME;UID=XX;PWD=YY;&quot;
  c.Open
  
  Query = Sheets(&quot;Query&quot;).Range(&quot;A2&quot;).Value &amp; Sheets(&quot;Query&quot;).Range(&quot;A3&quot;).Value &amp; Sheets(&quot;Query&quot;).Range(&quot;A4&quot;).Value /cells where my query is located/
  
  rs.Open Query
  End Sub
  </code></pre>
  <p>This works fine. Now, I tried to create a new macro called FDW_Connect and place that connection string in that macro, so that I can reference that FDW_Connect in all other macros (removing the need to update the PWD every time I change it).</p>
  <pre><code>Sub Only_Active_Hierarchy()
  
  Dim rs As ADODB.Recordset, r As Range, Query As String, c As New ADODB.Connection
  Set c = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  Query = Sheets(&quot;Query&quot;).Range(&quot;A2&quot;).Value &amp; Sheets(&quot;Query&quot;).Range(&quot;A3&quot;).Value &amp; Sheets(&quot;Query&quot;).Range(&quot;A4&quot;).Value
  
  Application.Run &quot;'PERSONAL.XLSB'!FDW_Connect&quot;
  
  c.Open
  rs.Open Query
  
  End Sub
  
  Sub FDW_Connect()
  
  Dim c As New ADODB.Connection, rs As ADODB.Recordset
  Set c = New ADODB.Connection
  Set rs = New ADODB.Recordset
  c.ConnectionString = &quot;Driver={Oracle in OraClient19Home1};DBQ=DBNAME;UID=XX;PWD=YY;&quot;
  c.Open
  
  End Sub
  </code></pre>
  <p>If I run the above code then I get the following error</p>
  <pre><code>Run-time error'3709':
  The connection cannot be used to perform this operation.It is either closed or invalid in this context.
  </code></pre>
  <p>In debugging mode, it points to the,</p>
  <pre><code>rs.Open Query
  </code></pre>
  <p>line when the 3709 error pops up. Any way I can resolve this?</p>