VBA‎ > ‎

VBA to create an ADO recordset against a SQL Server database

 
 
 
Dim Cn2 As ADODB.Connection
Dim objRS2 As ADODB.Recordset
Set objRS2 = CreateObject("ADODB.Recordset")
Set Cn2 = New ADODB.Connection
Cn2.ConnectionString = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=Database;Network=Network;User Id=sa;Password=password"
Cn2.ConnectionTimeout = 40
Cn2.Open
Dim strSQL2 As String
strSQL2 = "Select * from table"
objRS2.Open strSQL2, Cn2, adOpenForwardOnly, adLockReadOnly
    If Not (objRS2.EOF And objRS2.BOF) Then
        Do While Not objRS2.EOF 'Start looping through the records
            'do something here
            objRS2.MoveNext
        Loop
    End If
    objRS2.Close
    Set objRS2 = Nothing

If Cn2.State <> adStateClosed Then
Cn2.Close
End If

Comments