title: How to write common functions to Open and Close database connection?
body: <p>I'm beginner in Excel VBA and your help is much appreciated.</p>
  
  <p>Please advice how can I create a common function to Open and database connection, and another function to close it, to avoid duplication of coding?</p>
  
  <hr>
  
  <p>Here is my code. I'm stuck on how to move on...</p>
  
  <pre><code>Const connection_string As String = "Provider=SQLOLEDB.1;Password=XXX;Persist Security              `Info=True;User ID=sa;Initial Catalog=TESTDB;Data Source=XXXX;"
  
  Sub DuplicateDBConnection()
  
  'Declare variables
  Set cn1 = New ADODB.Connection
  Set cmd1 = New ADODB.Command
  Set rs1 = New ADODB.Recordset
  
  'Open Connection
  cn1.ConnectionString = connection_string
  cn1.Open
  
  'Set and Excecute SQL Command
  Set cmd1.ActiveConnection = cn1
  cmd1.CommandText = "Select stock_code, name, sector_id from stock_master"
  cmd1.CommandType = adCmdText
  cmd1.Execute
  
  'Open Recordset
  Set rs1.ActiveConnection = cn1
  rs1.Open cmd1
  
  'Copy Data to Excel
  ActiveSheet.Range("A1").CopyFromRecordset (rs1)
  
  'Close Connection
  rs1.Close
  cn1.Close
  
  'Throw Object
  Set rs1 = Nothing
  Set cn1 = Nothing
  
  End Sub
  </code></pre>
  
  <hr>
  
  <p>My wish is to write common functions so that I don't need to keep writing code to connect and close connection. </p>
  
  <pre><code>Sub ConnectDB()
      'Codes to connect DB
  End Sub
  
  Sub CloseConnnection()
      'Codes to close connection
  End Sub
  
  Sub ExecuteCode()
      ConnectDB
      'Execute SQL command to manipulate data on excel and SQL database 
      CloseConnection
  End Sub 
  </code></pre>
  
  <hr>
  
  <p>Edit based on Kittoe's suggestions and works properly now. Thanks!</p>
  
  <ol>
  <li><p>Class:
  a. Created a class called AdoDbHelper, Private Instancing 
  b. In AdoDbHelper, change "Option Compare Database" to "Option Compare Text"</p></li>
  <li><p>Module:
  Create a function like this.</p></li>
  </ol>
  
  <p>Code below:</p>
  
  <pre><code>Const connection_string As String = "Provider=SQLOLEDB.1;Password=XXX;Persist Security              `Info=True;User ID=sa;Initial Catalog=TESTDB;Data Source=XXXX;"
  
  Sub Test()
  
  Dim sourceDb As New AdoDbHelper
  Dim sourceRs As New ADODB.Recordset
  
  sourceDb.Connect (connection_string)
  
  Set sourceRs = sourceDb.OpenRecordset("Select stock_code, name, sector_id from     stock_master")
  
  With sourceRs
      'Do stuff!
  
       ActiveSheet.Range("A1").CopyFromRecordset sourceRs
  
      .Close
  End With
  
  sourceDb.Disconnect
  
  Set sourceRs = Nothing
  Set sourceDb = Nothing
  
  End Sub
  </code></pre>