title: Share adodb Connection Between modules VBA
body: <p>Is there a basic way to create a database connection in one module/form then be able to access the same database connection (left open) from another module?</p>
  
  <p>I have a form that pops up requesting the SQL server, then from that gathers the databases for the user to select. Then when i go to try and use the connection it does not connect to the db declared Public in the other module?</p>
  
  <p>Does this make sense and if so is there a way around? </p>
  
  <pre><code>Public conn As ADODB.Connection
  Public Function openConnection(Optional DB As String)
  Dim str As String
  str = "Provider=SQLOLEDB;Server=" &amp; Me.tbx_serverName.Text
  If (Not IsEmpty(DB)) Then
  str = str &amp; ";Database=" &amp; DB
  End If
  str = str &amp; ";UID=" + tbx_dbuser.Text + " ;PWD=" + tbx_dbpass.Text + ";"
  
  On Error Resume Next
      If conn Is Nothing Or conn.Status = 0 Then
        Set conn = New ADODB.Connection
        conn.Open str
      End If
  
  If (conn.State = adStateOpen) Then
  If (Not IsEmpty(DB)) Then
  Me.lbl_connecteddb.Caption = "Connected to Database:" + DB
  Else
  Me.lbl_connecteddb.Caption = "Connected to Database:" + Me.ComboBox1.SelText
  End If
  Else
  Me.lbl_connecteddb.Caption = "Error"
  End If
  
  End Function
  </code></pre>
  
  <p>That is my open connection function, but i cannot seem to access (correctly) the conn in another module, 
  I.E set rs = conn.execute("SELECT * FROM BLAH")</p>