title: Set directory for data source in VBA
body: <p>The following code in VBA is used to connect to the Access data file:</p>
  
  <pre><code>Public Const sConnect As String = "Provider = Microsoft.ACE.OLEDB.12.0;" &amp; _
                                "Data Source = D:\data.accdb"
  </code></pre>
  
  <p>I am trying to set the directory for the data source from MS Access by using the functions like <code>ActiveWorkbook.Path</code> or <code>CurDir()</code>:</p>
  
  <pre><code>Public data_source As String
  data_source = ThisWorkbook.Path &amp; Application.PathSeparator &amp; "data.accdb"
  </code></pre>
  
  <p>and then:</p>
  
  <pre><code>Public Const sConnect As String = "Provider = Microsoft.ACE.OLEDB.12.0;" &amp; _
                                    "Data Source = data_source "
  </code></pre>
  
  <p>but it didn't work and the program said "Invalid outside procedure". It seems that <code>ActiveWorkbook.Path</code> works only inside a Sub? Is there any way to do this properly? Thank you a lot.</p>