ADO and the Command Object - CommandType is adCmdTable (Page 5 of 6 )
On SQL 2005 Server
When the CommandType is assigned to the adCmdTable, what is returned is equivalent to returning all the columns from the named table as shown in the next picture.
Using ADO
In order to return the same records using ADO, the properties CommandType and CommandText have to be appropriately assigned. The next code listing in the click event of a Command Button does exactly that as shown by the returned recordset that follows the code listing.
Private Sub Command2_Click()
'declare Connection, Command and RecordSet variables
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim rst As ADODB.Recordset
'create new instances of the variables
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
'Open the connection
oConn.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
oCmd.ActiveConnection = oConn
'set command's CommandType property to adCmdTable
oCmd.CommandType = adCmdTable
'set the Command's Command text property to a name of a table
'as a string
oCmd.CommandText = "Employees"
Set rst = oCmd.Execute
'note although all 18 columns are returned, only three of them are
'printed to the immediate screen.
Do Until rst.EOF
Debug.Print (rst.Fields(0).Value & " , " & _
rst.Fields(1).Value & " , " & _
rst.Fields(2).Value & vbCrLf)
rst.MoveNext
Loop
MsgBox (rst.Fields.Count)
MsgBox (oCmd.CommandText)
oConn.Close
End Sub
The result printed out to the immediate screen when the Command button is clicked is shown in the next paragraph.
1 , Davolio , Nancy
2 , Fuller , Andrew
3 , Leverling , Janet
4 , Peacock , Margaret
5 , Buchanan , Steven
6 , Suyama , Michael
7 , King , Robert
8 , Callahan , Laura
9 , Dodsworth , Anne
Next: CommandType is adCmdUnknown >>
More Database Articles
More By Jayaram Krishnaswamy
