Returning Recordcount with ADO
Tested with VB6 SP1
This VB Tip is provided
courtesy of our friend Aslan. When
you're done here, be sure to check out her VBDelight web site for more delightful
The following ADO code should successfully
return a RecordCount for the Shippers table in the Northwind sample database that comes
with Visual Basic, before and after updating the database with a new value.
Test the following code in New StdEXE project.
1. Add a Reference to the Microsoft ActiveX Data Objects 2.0 Library.
2. Add two Label controls to the Form.
3. Paste the following code in the General Declarations section of your code window.
Private cn As New
Private cmd As New
Private rs As New
Private Sub Form_Load()
' This is the
key step, you must use a Client Side Cursor.
cn.CursorLocation = adUseClient
' You will
have to change the path to point at the NorthWind database.
cn.Open "Provider=MSDASQL;" & _
Access Driver (*.mdb)};" & _
Files\Microsoft Visual Studio\VB98\NWIND.MDB;"
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from Shippers"
Set cmd.ActiveConnection = cn
' Open the
rs.Open cmd, , adOpenKeyset, adLockOptimistic
' Display the
record count before adding a new record.
Label1.Caption = "Records in recordset = " &
' Add a new
rs.Fields("CompanyName") = "Federal Express"
' Display the
record count after adding a new record.
Label2.Caption = "Records in recordset = " &