Connect to SQL Server database using VB

 
To connect to SQL Server Database, the Steps involved are
 
1. Define A Connection
 
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Data Source=(name of database server);User id=sa;password=sa;Initial Catalog=<databasename>"
cnn.Open
 
2. Define a RecordSet
 
Public rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
3. Now, use the recordset to connect to database using connection object
 
rs.CursorLocation = adUseClient
rs.Open "Select field1, field2 from table1", cnn
 
4. You can use the data returned by query in recordset like this
 
while rs.Eof = false and rs.bof = false
         me.txtField1 = rs("field1")
         rs.movenext
wend
 
Note: Do not forget to use movenext function to move the recordset to next record
otherwise, it will result endless loop.
         The recordset can also be moved backward, moved to first and last at once.
 
5. Finally, do not forget to close the recordset and connection.
But, they are automatically dropped after the application is closed.
 
rs.close
cnn.close
 
set cnn = Nothing