0 +)
(Access 2000 [OLEDB])
adIDispatch 9
Continued
CHAPTER 2 n DATA IN, DATA OUT 70
Data Type Value Access SQL Server
adInteger 3 AutoNumber Identity (SQL
Integer Server 6.5)
Long Int
adLongVarBinary 205 OLEObject Image
adLongVarChar 201 Memo (Access 97) Text
Hyperlink (Access 97)
adLongVarWChar 203 Memo (Access 2000 NText (SQL Server 7.0 +)
[OLEDB])
Hyperlink (Access
2000 [OLEDB])
adNumeric 131 Decimal (Access 2000 Decimal
[OLEDB]) Numeric
adSingle 4 Single Real
adSmallInt 2 Integer SmallInt
adUnsignedTinyInt 17 Byte TinyInt
adVarBinary 204 ReplicationID VarBinary
(Access 97)
adVarChar 200 Text (Access 97) VarChar
adVariant 12 Sql_Variant (SQL
Server 2000 +)
adVarWChar 202 Text (Access 2000 NVarChar (SQL
[OLEDB]) Server 7.0 +)
adWChar 130 NChar (SQL Server 7.0 +)
The remainder of our code is basically identical to our previous examples. Listing 2-5
shows what the finished subroutine looks like.
Listing 2-5. Calling Parameterized SQL in VBA
Sub GetManagerEmployeeListSQL()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim xlSheet As Worksheet
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim i As Integer
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A3").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
CHAPTER 2 n DATA IN, DATA OUT 71
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.
Pages:
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97