How can i access sqlserver from VB using ADO?

Well quite easy actualy.. not difficult as you think.. first for a sample we'll create a database in the SQLServer which we will access using ADO.

With SQL, there is SQL Enterprise manager, which is a graphical utility which allows u to create databases and tables using a wizard.. like creating tables in Acess.. i guess u will be able to doit.

Then to access data in a table in that database[say MyDB] u have couple of optoins

1) Connect straight to SQLServer[by sqlserver name, login, password]
using ADO

2) Create a System DSN which points to MyDB Database in SQLServer, and with ADO, you open the DSN, thus accessing the Database

The ways to retrieve data is alsmost same like getting data from an access database. like u connect to a Accessdatabse using a DSN. u can connect to sqlserver using a DSN, if u have create a DSN, which points to the SQLServer database.

then say; if we have a users table and we want to retrieve all the users using ADO;

dim objconn as newa adodb.connection
dim objrs as new adodb.recordset
dim strsql as string

'open the connection using a DSN
'then construct a sql statement to 'retrieve all the users from the users 'table

strsql="select * from users"

'then pass it to ADO connection 'objects.execute method which will give 'u a recordset which is readonly and 'forward.

set objrs=objconn.execute(strsql)

'then u can go through the recordset 'and do whatever u want with the 'recordset

do while not objrs.eof
cmbusername.add objrs("UserName")
objrs.movenext
loop


hope this gives u any idea.. just that cause u want to retrieve data from SQLSERVER methods are same as u will do to retrieve data from an access table...

Look also at here:
"Beginning Visual Basic 6 Database Programming"
http://msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/partbook/vb6/beginningvisualbasic6databaseprogramming.htm

Here you can find great amount of useful literatures!

Create a ODBC datasource

then use the following code..
str = PROVIDER=MSDASQL;dsn=<dsn name>;uid=sa;pwd=;database=<database name>;

  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open str

Private Sub Form_Load()
 

  Set adoPrimaryRS = New Recordset

  adoPrimaryRS.Open "SQL", db, adOpenStatic, adLockOptimistic

  Dim oText As TextBox
  'Bind the text boxes to the data provider
  For Each oText In Me.txtFields
    Set oText.DataSource = adoPrimaryRS
  Next

  mbDataChanged = False
End Sub

Private Sub cmdAdd_Click()
  On Error GoTo AddErr
  With adoPrimaryRS
    If Not (.BOF And .EOF) Then
      mvBookMark = .Bookmark
    End If
    .AddNew
    lblStatus.Caption = "Add record"
    mbAddNewFlag = True
    SetButtons False
  End With

  Exit Sub
AddErr:
  MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
  On Error GoTo DeleteErr
  With adoPrimaryRS
    .Delete
    .MoveNext
    If .EOF Then .MoveLast
  End With
  Exit Sub
DeleteErr:
  MsgBox Err.Description
End Sub

Private Sub cmdRefresh_Click()
  'This is only needed for multi user apps
  On Error GoTo RefreshErr
  adoPrimaryRS.Requery
  Exit Sub
RefreshErr:
  MsgBox Err.Description
End Sub

Private Sub cmdEdit_Click()
  On Error GoTo EditErr

  lblStatus.Caption = "Edit record"
  mbEditFlag = True
  SetButtons False
  Exit Sub

EditErr:
  MsgBox Err.Description
End Sub
Private Sub cmdCancel_Click()
  On Error Resume Next

  SetButtons True
  mbEditFlag = False
  mbAddNewFlag = False
  adoPrimaryRS.CancelUpdate
  If mvBookMark > 0 Then
    adoPrimaryRS.Bookmark = mvBookMark
  Else
    adoPrimaryRS.MoveFirst
  End If
  mbDataChanged = False

End Sub

Private Sub cmdUpdate_Click()
  On Error GoTo UpdateErr

  adoPrimaryRS.UpdateBatch adAffectAll

  If mbAddNewFlag Then
    adoPrimaryRS.MoveLast              'move to the new record
  End If

  mbEditFlag = False
  mbAddNewFlag = False
  SetButtons True
  mbDataChanged = False

  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub cmdFirst_Click()
  On Error GoTo GoFirstError

  adoPrimaryRS.MoveFirst
  mbDataChanged = False

  Exit Sub

GoFirstError:
  MsgBox Err.Description
End Sub

Private Sub cmdLast_Click()
  On Error GoTo GoLastError

  adoPrimaryRS.MoveLast
  mbDataChanged = False

  Exit Sub

GoLastError:
  MsgBox Err.Description
End Sub

Private Sub cmdNext_Click()
  On Error GoTo GoNextError

  If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
  If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 Then
    Beep
     'moved off the end so go back
    adoPrimaryRS.MoveLast
  End If
  'show the current record
  mbDataChanged = False

  Exit Sub
GoNextError:
  MsgBox Err.Description
End Sub

Private Sub cmdPrevious_Click()
  On Error GoTo GoPrevError

  If Not adoPrimaryRS.BOF Then adoPrimaryRS.MovePrevious
  If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
    Beep
    'moved off the end so go back
    adoPrimaryRS.MoveFirst
  End If
  'show the current record
  mbDataChanged = False

  Exit Sub

GoPrevError:
  MsgBox Err.Description
End Sub

Private Sub SetButtons(bVal As Boolean)
  cmdAdd.Visible = bVal
  cmdEdit.Visible = bVal
  cmdUpdate.Visible = Not bVal
  cmdCancel.Visible = Not bVal
  cmdDelete.Visible = bVal
  cmdClose.Visible = bVal
  cmdRefresh.Visible = bVal
  cmdNext.Enabled = bVal
  cmdFirst.Enabled = bVal
  cmdLast.Enabled = bVal
  cmdPrevious.Enabled = bVal
End Sub



if you have any doubts ple ask..

Regards..
Raj

Just looking....

Here's a thought:

    Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim strMessage As String
    Dim intRows As Integer
    Dim avarRecords As Variant
    Dim intRecord As Integer

    ' Open recordset with names and hire dates from employee table.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open "SELECT fName, lName, hire_date " & _
        "FROM Employee ORDER BY lName", strCnn, , , adCmdText

    Do While True
        ' Get user input for number of rows.
        strMessage = "Enter number of rows to retrieve."
        intRows = Val(InputBox(strMessage))

        If intRows <= 0 Then Exit Do

        ' If GetRowsOK is successful, print the results,
        ' noting if the end of the file was reached.
        If GetRowsOK(rstEmployees, intRows, _
                avarRecords) Then
            If intRows > UBound(avarRecords, 2) + 1 Then
                Debug.Print "(Not enough records in " & _
                    "Recordset to retrieve " & intRows & _
                    " rows.)"
            End If
            Debug.Print UBound(avarRecords, 2) + 1 & _
                " records found."

            ' Print the retrieved data.
            For intRecord = 0 To UBound(avarRecords, 2)
                Debug.Print "  " & _
                    avarRecords(0, intRecord) & " " & _
                    avarRecords(1, intRecord) & ", " & _
                    avarRecords(2, intRecord)
            Next intRecord
        Else
            ' Assuming the GetRows error was due to data
            ' changes by another user, use Requery to
            ' refresh the Recordset and start over.
            If MsgBox("GetRows failed--retry?", _
                    vbYesNo) = vbYes Then
                rstEmployees.Requery
            Else
                Debug.Print "GetRows failed!"
                Exit Do
            End If
        End If

        ' Because using GetRows leaves the current
        ' record pointer at the last record accessed,
        ' move the pointer back to the beginning of the
        ' Recordset before looping back for another search.
        rstEmployees.MoveFirst
    Loop

    rstEmployees.Close

For Updating, inserting or deleteing data you should use stored procedures that do each of these separately.  The VB code would loop through an array or collection to do this with multiple rows of data.  Then in each loop you call a stored procedure.  You pass the deletion stored procedure the Key ID field for a deletion:

Create Stored procedure dbo.DeleteData
  (@IDKey as varchar(12)) -- or whatever datatype the key is

  DELETE dbo.TableName t
  WHERE t.KeyID = @IDKey


The insert or update procedures would be passed all the values of a record:

Create dbo.InsertTableRow
  (
   @Field1 varchar(12),
   @Field2 int,
   @Field3 datetime,
   -- and so on for as may columns
   @Fieldn varchar(16)
   )

  INSERT dbo.Tablename t
    (
     Field1,
     Field2,
     Field3,
     Fieldn
     )
  VALUES
    (
     Field1,
     Field2,
     Field3,
     Fieldn
     )
 

-- or for Updating a table:

Create Procedure dbo.UpdateTable
  (
   @KeyID varchar(12),  -- you need a key row identifier for an update
   @Field1 varchar(12),
   @Field2 int,
   @Field3 datetime,
   -- and so on for as may columns
   @Fieldn varchar(16)
   )

  UPDATE dbo.Tablename
  SET
    Field1 = @Field1,
    Field2 = @Field2,
    Field3 = @Field3,
    Fieldn = @Fieldn
  WHERE
    KeyID = @KeyID

Thanks to you all for coming to the rescue so quickly.
I'll try these codes before i choose one as the answer. I hope you wouldn't mind if i contact you again in case i faced some particular problems with the codes.
Another questions to those who've already provided me with answers:
Someone proposed this to me and i was wondering if it is possible to actually connect to  SQL server without using an ODBC connection?

I don't know if you can do this through anything other than C, or perhaps a dll reference trhrough VB:

A SQLOLEDB session represents a single connection to a server running Microsoft® SQL Server™.

OLE DB requires that sessions delimit transaction space for a data source. All command objects created from a specific session object participate in the local or distributed transaction of the session object.

The first session object created on the initialized data source receives the SQL Server connection established at initialization. When all references on the interfaces of the session object are released, the SQL Server connection becomes available to another session object created on the data source.

An additional session object created on the data source establishes its own connection to the SQL Server installation as specified by the data source. The SQL Server connection is dropped when the application releases all references to objects created that session.

Thanks alot Jon Raymond... I'm really amazed at the speed and contents of your replies.

To Jon, soosairaj, and halapaya:
The question remains though, is it possible to connect to SqlServer through VB, without using ODBC connection? Can you point me to a specific source that can direct me through this.

Here's what i'm planning to do:
trying to create a simple VB project tha can access a dummy database 'PUBS" that lies on an SQL Server, my aim is to create a simple code that can manupilate data using VB code from a sample table (dbo.authors) & be able to Handle errors. Then I need to enable SQL Query and specific Select statements to be applied.

Here's a sample of what i've done, some parts i've not even tried to do yet and what all of you have given me was very helpful as you can see:

-------------------------------------
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSql As String

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr

    '...... to de done

Exit Sub
DeleteErr:
        MsgBox Err.Description
End Sub

Private Sub cmdEnd_Click()
    End
End Sub

Private Sub cmdGoFirst_Click()
On Error GoTo GoFirstError
   
    rs.MoveFirst
    Me.txtauid = rs.Fields("au_id")
    Me.txtau_lname = rs.Fields("au_lname")
    Me.txtau_fname = rs.Fields("au_fname")
   
Exit Sub
GoFirstError:
        MsgBox Err.Description
End Sub


Private Sub cmdGoLast_Click()
On Error GoTo GoLastError

    rs.MoveLast    '...... not working!!!
    Me.txtauid = rs.Fields("au_id")
    Me.txtau_lname = rs.Fields("au_lname")
    Me.txtau_fname = rs.Fields("au_fname")
   
Exit Sub
GoLastError:
        MsgBox Err.Description
End Sub

Private Sub cmdGoNext_Click()
On Error GoTo GoNextError
   
    rs.MoveNext
    Me.txtauid = rs.Fields("au_id")
    Me.txtau_lname = rs.Fields("au_lname")
    Me.txtau_fname = rs.Fields("au_fname")
   
Exit Sub
GoNextError:
        MsgBox Err.Description

End Sub

Private Sub cmdGoPrevious_Click()
On Error GoTo GoPrevError
   
    rs.MovePrevious   '...not working!!!
    Me.txtauid = rs.Fields("au_id")
    Me.txtau_lname = rs.Fields("au_lname")
    Me.txtau_fname = rs.Fields("au_fname")
   
Exit Sub
GoPrevError:
        MsgBox Err.Description

End Sub

Private Sub cmdInsert_Click()
On Error GoTo InsertErr

    '...... to be done!
 
Exit Sub
InsertErr:
        MsgBox Err.Description

End Sub

Private Sub Form_Load()

Call OpenConnection

Dim oText As TextBox
'Binding text boxes to data provider
    'txtauid     <> "au_id"
    'txtau_lname <> "au_lname"
    'txtau_fname <> "au_fname"
For Each oText In Me.txtFields
   Set oText.DataSource = rs
Next

rs.MoveFirst
End Sub

Sub OpenConnection()
   
    Set cn = New ADODB.Connection

    cn.Open "Providor=sqloledb;" & _
    "Data Source= PUBS; USER Id = fmiaa1;Password=password;"
   
    Set rs = New ADODB.Recordset
    rs.Open "SELECT au_id, au_lname, au_fname FROM dbo.authors", cn, , , adCmdText
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
-------------------------------------

Question to soosairaj:
I have tried to use to Bind the text boxes to the recordset, which obviously allows more room for data handeling. It triggers this error message:
'Method or data member not found'
Now, am i correct to think that i need to bind each text box on my form individually?

Note to halapaya:
i like the simple and practical way you've put out your proposed solution, thanks! but as you can see, i got alot more on my hands to fix!

Note to chigrik:
couldn't access the URL you specified? any idea why i couldn't?

At first glance it looks like you are using rs as a module level variable.  That is you expect rs to maintain its value throughout the form.  Then you are binding rs to each text box on loading.  There may be a conflict with binding and then explicitly setting the text box values upon command.  I would try leaving the text boxes unbound first.  That might solve it.  If you're setting the text box values why would you need to bind them?

As to ODBC.  Why not?  I haven't seen any VB connection method that doesn't use it at some point.  Then there's SQLDMO which initially uses ODBC to connect.  But, then you can manipulte SQL Server objects by reference, since SQLDMO is a refernce you also have to check.  It is used mostly for running backups or other server management functions.

Here's a non ODBC connection method, though I'm not real familiar with it:
-- OLE DB --
ADO clients that communicate with OLE DB need an OLE DB provider, a dynamic-link library that uses OLE DB interfaces and methods to query an SQL data source. For Microsoft® SQL Server™, two types of OLE DB providers can be used:

Microsoft OLE DB Provider for SQL Server (SQLOLEDB), which directly maps OLE DB interfaces and methods over SQL Server data sources.
Microsoft OLE DB Provider for ODBC (MSDASQL), which maps OLE DB interfaces and methods to ODBC APIs. OLE DB consumers connect to a SQL Server database through an existing SQL Server ODBC driver.
We recommend using SQLOLEDB, supplied with SQL Server, as your OLE DB provider. SQLOLEDB maps OLE DB interfaces and methods over SQL Server data sources without using the ODBC API or the SQL Server ODBC driver as intermediaries. The ADO Introductory Visual Basic Sample application, used in most of the code examples in the ADO object model topics, uses SQLOLEDB.

There are differences between SQLOLEDB and MSDASQL. The two providers support different connection properties, and SQLOLEDB does not support the use of an ODBC DSN connection. For more information about the use of connection properties for the two providers, see Connection Object.

In general, if you are exercising ADO code with SQL Server and an unexpected behavior occurs, check the provider properties, because the unexpected behavior could be attributable to ADO interacting differently with a different OLE DB provider.

Jon Raymond, i greatly appreciate your efforts, the speed and contents of your response have grealty helped me understand this whole thing much better. This's exactly and precisely what code I needed.
It was closest to the heart fo my problem and thus, i'm accepting it as an answer.
If i wouldn't be imposing, hope you won't mind me contacting you directly in case i face certain problems while programming.

Here's my 'functional' code:

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSql As String

'working....
Private Sub cmdBack_Click()
    Me.Hide
    ADO_in_VB.Show
End Sub

'working....
Private Sub cmdDelete_Click()
On Error GoTo DeleteErr

If MsgBox("Do you want to delete " + txtau_lname, vbYesNo, "") = vbYes Then
    rs.Delete
    rs.MoveFirst
    CallRecords
End If

Exit Sub
DeleteErr:
        MsgBox Err.Description
End Sub

'working....
Private Sub cmdEnd_Click()
    End
End Sub

'working....
Private Sub cmdGoFirst_Click()
On Error GoTo GoFirstError
   
    rs.MoveFirst
    CallRecords
   
Exit Sub
GoFirstError:
        MsgBox Err.Description
End Sub

'working....
Private Sub cmdGoLast_Click()
On Error GoTo GoLastError
   
    rs.MoveLast
    CallRecords

Exit Sub
GoLastError:
        MsgBox Err.Description
End Sub

'working....
Private Sub cmdGoNext_Click()
On Error GoTo GoNextError
 
 If Not rs.EOF Then
    rs.MoveNext
    CallRecords
 Else
    rs.MoveLast
    MsgBox "This is the last record", vbExclamation
 End If

Exit Sub
GoNextError:
        MsgBox Err.Description

End Sub

'working....
Private Sub cmdGoPrevious_Click()
On Error GoTo GoPrevError
If Not rs.BOF Then
    rs.MovePrevious
    CallRecords
Else
    rs.MoveFirst
    MsgBox "This is the first record", vbExclamation
End If

Exit Sub
GoPrevError:
        MsgBox Err.Description

End Sub

'working....
Private Sub cmdInsert_Click()
On Error GoTo InsertErr

If MsgBox("Do you want to Insert New Record?", vbYesNo, "") = vbYes Then
    Me.txtauid = ""
    Me.txtau_lname = ""
    Me.txtau_fname = ""
    rs.AddNew
    rs.Fields("au_id") = Me.txtauid
    rs.Fields("au_lname") = Me.txtau_lname
    rs.Fields("au_fname") = Me.txtau_fname
    rs.Update
    CallRecords
End If
Exit Sub
InsertErr:
        MsgBox Err.Description

End Sub

'working....
Private Sub Form_Load()
Call OpenConnection

'SHOWING RECORDS AT LOADING TIME
    rs.MoveFirst
    CallRecords

End Sub

'working....
Sub OpenConnection()
 
    Set cn = New ADODB.Connection
                     
    'Check if i can connect to the Server Provider directly? YES
    'cn.Open "PROVIDER=SQLOLEDB.1;" &
    '"Server=Gascogibssrv1;Database=pubs;UID=fmiaa1;PWD=password"
   
    'Connecting using an Data Link File (.udl)
    'it's on the root Directory (c:\pubs.udl)
    cn.ConnectionString = "File Name=" & "C:\pubs.udl" & ";"
    cn.Open

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockBatchOptimistic
    rs.Open "SELECT au_id, au_lname, au_fname FROM dbo.authors", cn, , , adCmdText
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Sub CallRecords()
    Me.txtauid = rs.Fields("au_id")
    Me.txtau_lname = rs.Fields("au_lname")
    Me.txtau_fname = rs.Fields("au_fname")
End Sub

You can contact me at jon_raymond@hotmail.com. But I can't guarantee I'll always have a fast response.  Glad I could help.