title: Connecting Excel VBA to sql server using ODBC Connections and Objects
body: <p>I am new to coding, i am recently working on a Stock Control Program using Excel and VBA, at first i can run and get results on my designer, but the more buttons and labels that i add on it becomes a problem to run!</p>
  
  <ul>
  <li>Error 1
  I Keep getting an automation error.</li>
  <li><p>I understood that the error was related to database connection, so i proceeded to connecting to the database. using the code bellow: </p>
  
  <pre><code>Sub DbConnection()
  Dim cn As ADODB.Connection
  Set cn = New ADODB.Connection
  Dim rs As ADODB.Recordset
  
  Dim strConn As String
  strConn = "Driver={SQL Server}; Server=servername; Database=Dbname; VID=username; PWD=password"
  
  cn.Open strConn
  cn.Close
  Set cn = Nothing
  MsgBox "Connected"
  End Sub
  </code></pre></li>
  <li><p>I used the Microsoft ActiveX Data Object 6.1 library.
  but i realized that it is not working and was recommended to use ODBC Connection, and now i am stuck, Can anyone help me on this?</p></li>
  <li><p>How do i use this connection for my ODBC Connection?</p></li>
  <li><p>Anything I am doing Wrong? Which Reference Object do i have to use?
  PS: I am using Ms Excel 2016 and SSMS 2017. Thanks </p></li>
  </ul>