Open sConnString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
nNote To connect to a named instance of SQL Server, the convention is to use a server name of the format
\. Note the way the Server property is set in our previous example:
Server=MyServerName\SQLEXPRESS.
CHAPTER 2 n DATA IN, DATA OUT 69
Now let??™s create our Parameter object, fill some of its properties, and add it to our Command
object.
Set param = New ADODB.Parameter
With param
.Name = "ManagerID"
.Type = adInteger
.Value = ActiveSheet.Range("A1").Value
End With
With cmd
.CommandType = adCmdStoredProc
.CommandText = "uspGetManagerEmployees"
.Parameters.Append param
End With
We are setting the Parameter object??™s Name property to ManagerID, as called for by the
stored procedure, and telling it to use the Integer data type. Finally, we set its Value property
to whatever value is contained in the active sheet??™s cell A1.
Once that??™s done, we set up our Command object by telling it what kind of command we
need (stored procedure), and the name of the stored procedure. Then we append our
Parameter object to the Command object??™s Parameters collection.
Table 2-4 gives a list of ADO data type enums, along with their actual values and the corresponding
Access and SQL data types they refer to.
Table 2-4. ADO Data Types
Data Type Value Access SQL Server
adBigInt 20 BigInt (SQL Server
2000 +)
adBinary 128 Binary
TimeStamp
adBoolean 11 YesNo Bit
adChar 129 Char
adCurrency 6 Currency Money
SmallMoney
adDate 7 Date DateTime
adDBTimeStamp 135 DateTime (Access 97 DateTime
[ODBC]) SmallDateTime
adDecimal 14
adDouble 5 Double Float
adGUID 72 ReplicationID UniqueIdentifier
(Access 97 [OLEDB]), (SQL Server 7.
Pages:
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96