Listing 7: Accessing Field Object Data

' Declare connection and connection string variables
Dim conSqlServer, csSqlServer

' Declare command and command text variables
Dim cmdContacts, ctContacts

' Declare recordset variable
Dim rsContacts

' Declare file system object and create file
Dim fsoContacts, txtContacts

' Declare argument constant
Const adStateOpen = 1

' Create Connection object
Set conSqlServer = CreateObject("ADODB.Connection")

' Define connection string
csSqlServer = "Provider='sqloledb';Data Source='ws04';" & _
  "Integrated Security='SSPI';Initial Catalog='AdventureWorks';"

' Open connection    
conSqlServer.Open csSqlServer
        
' Create Command object and set the connection
Set cmdContacts = CreateObject("ADODB.Command")
Set cmdContacts.ActiveConnection = conSqlServer

' Define and set the command text
ctContacts = "SELECT TOP 10 FirstName, LastName FROM Person.Contact"
cmdContacts.CommandText = ctContacts

' Create and populate recordset object
Set rsContacts = CreateObject("ADODB.Recordset")
rsContacts.Open cmdContacts

' Create FileSystemObject and file
Set fsoContacts = CreateObject("Scripting.FileSystemObject")
Set txtContacts = fsoContacts.CreateTextFile("C:\Info\SqlServerContacts.txt", True)

' Implement loop to display result set
Do Until rsContacts.EOF
  txtContacts.WriteLine(rsContacts.Fields("FirstName").Value & _
  " " & rsContacts.Fields("LastName").Value)
rsContacts.MoveNext 
Loop 

' Clean up
If rsContacts.State = adStateOpen then
  rsContacts.Close
End If

If conSqlServer.State = adStateOpen then
  conSqlServer.Close
End If

Set conSqlServer = Nothing
Set csSqlServer = Nothing
Set cmdContacts = Nothing
Set ctContacts = Nothing
Set rsContacts = Nothing
Set fsoContacts = Nothing
Set txtContacts = Nothing