Using ADO with SQL Native Client (SQL Server 2005)

Using ADO with SQL Native Client (SQL Server 2005 Books Online, September 2007)

Enabling SQL Native Client from ADO

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Retrieving XML Column Data

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sXMLResult As String

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;"

con.Open

' Get the xml data as a recordset.
Set rst.ActiveConnection = con
rst.Source = "SELECT AdditionalContactInfo FROM Person.Contact " _
   & "WHERE AdditionalContactInfo IS NOT NULL"
rst.Open

' Display the data in the recordset.
While (Not rst.EOF)
   sXMLResult = rst.Fields("AdditionalContactInfo").Value
   Debug.Print (sXMLResult)
   rst.MoveNext
End While

con.Close
Set con = Nothing

Retrieving UDT Column Data

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim strOldUDT As String
Dim strNewUDT As String
Dim aryTempUDT() As String
Dim strTempID As String
Dim i As Integer

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;"

con.Open

' Get the UDT value.
Set cmd.ActiveConnection = con
cmd.CommandText = "SELECT ID, Pnt FROM dbo.Points.ToString()"
Set rst = cmd.Execute
strTempID = rst.Fields(0).Value
strOldUDT = rst.Fields(1).Value

' Do something with the UDT by adding i to each point.
arytempUDT = Split(strOldUDT, ",")
i = 3
strNewUDT = LTrim(Str(Int(aryTempUDT(0)) + i)) + "," + _
   LTrim(Str(Int(aryTempUDT(1)) + i))

' Insert the new value back into the database.
cmd.CommandText = "UPDATE dbo.Points SET Pnt = '" + strNewUDT + _
   "' WHERE ID = '" + strTempID + "'"
cmd.Execute

con.Close
Set con = Nothing

Enabling and Using MARS

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Dim recordset1 As New ADODB.Recordset
Dim recordset2 As New ADODB.Recordset

Dim recordsaffected As Integer
Set recordset1 =  con.Execute("SELECT * FROM Table1", recordsaffected, adCmdText)
Set recordset2 =  con.Execute("SELECT * FROM Table2", recordsaffected, adCmdText)

con.Close
Set con = Nothing