Here's a sample code u can try out. But it uses ADO.
To set ADO reference, go to Project menu in VB, Click on References & look for "Microsoft ActiveX Data Object 2.x Library" (where x=0/1/5/6) & click on ok.
'Global variables
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset
Private Function DBConn() As Boolean
'Error trap handling
On Error GoTo ConnErr
'Local variables
Dim szServer As String, _
szDB As String, _
szUsername As String, _
szPassword As String
'Initialization
conn.CursorLocation = adUseClient
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
'Check connection state
'if previously open then close it (else it will give an error)
If conn.State <> 0 Then conn.Close
'Collect data
'Name of machine SQL is installed on
szServer = "myServer_name"
'Name of the database to connect to
szDB = "myDatabase_name"
'User name (usually sa)
szUsername = "myLogin_name"
'Password
szPassword = "myPassword"
'Open connection to...
conn.Open "DRIVER={SQL Server};Server=" & szServer & ";Database=" & szDB & ";UID=" & szUsername & ";PWD=" & szPassword
Set cn = conn
'VOILA!!!
DBConn = True
Exit Function
ConnErr:
'Ooops!!! 🙁
MsgBox "Could not connect to database!", vbCritical + vbOKOnly + vbApplicationModal + vbDefaultButton1, "DB connection: " & Err.Number
DBConn = False
End Function
Private Function GetSQL(prmSelect As String) As Recordset
'Local variable
Dim r As New ADODB.Recordset
'Run query
Set r = conn.Execute(prmSelect)
'Check for valid records
If r.RecordCount = 0 Then
Set GetSQL = Nothing
Exit Function
End If
'Return recorset
Set GetSQL = r
End Function
Private Sub cmdConn_Click()
'Connect
If Not DBConn Then Exit Sub
'Get record
Set rs = GetSQL("Select * FROM Test")
Do While Not rs.EOF
MsgBox "The value is " & rs!Col_Name
rs.MoveNext
Loop
End Sub
