Option Explicit

Dim conn As New ADODB.Connection ' This declares the name of the connection
Dim rs As New ADODB.Recordset    ' This declares the name of the recordset
Dim sSQL As String               ' Variable used to open the recordset

Private Sub Command1_Click()

    Dim dbtest  As String        ' Connection string variable
    Dim dbstate As Long

    dbtest = "Provider=sqloledb;" & _
             "DRIVER={SQLServer};" & _
             "SERVER=servername;" & _
             "DATABASE=database name;" & _
             "UID=******;" & _
             "PASSWORD=*******"   ' Connection String
    conn.Open dbtest
    dbstate = conn.State

    sSQL = "SELECT * FROM stafftb"
    rs.Open sSQL, conn ' Open the records Staff_Name

    While Not (rs.EOF) ' Until the end of the recordset is reached
        MsgBox rs(0)   ' Displays the first field of the current record
        rs.MoveNext
    Wend

    rs.Close
    conn.Close
    Set rs = Nothing

End Sub

dbtest = "Provider=sqloledb;DRIVER={SQLServer};SERVER=servername;DATABASE=database name;UID=******;PASSWORD=*******" 'Connection String"

dbtest = "Provider=sqloledb;Data Source=servername;Initial Catalog=databasename;Uid=******;Pwd=*******;"