PDA

View Full Version : Need empty recordset for almost complete code



tatetiff
04-20-2011, 03:51 PM
I have a loop below that goes into a sheet and collects all the sql queries and feeds them to the function but in order for it to work i need an empty recordset before the loop and the recordset then open and close the recordset for each cell within the loop: could some one help me this is what i have so far. the lines in bold are my attempt at creating empty record set please help




Function getrecordset(str) As String
'create recordset object
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
With rs
.ActiveConnection = cn
'using the str variable open the recordset
.Source = str
.Open
End With
'if recordset NOT NULL
If Not (rs.BOF And rs.EOF) Then
getrecordset = rs(0)
getrecordset = Recordset
'else
'getrecordset = "No Result"
getrecordset = vbNullString
End If
'close and destroy recordset object
Set rs = Nothing
End Function
Sub getresults()
Dim r As Integer
Dim c As Integer
Dim str As String
Dim SQL As String
Dim WhichWorksheet As String
Dim NamedRange As String
Dim rs As adodb.Recordset
Dim cn As adodb.Connection

Set cn = New adodb.Connection

Call CheckConnection

r = 31
With rs
.addnew
.Range("Environ1") = Range(c).Value
.Range("Environ3") = Range(c).Value
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


Set MFrst = Nothing
If MFcnn.State = 1 Then
CurrentStatus = "Updating"

Set rs = New adodb.Recordset
For c = 31 To 35
For r = 2 To 39
If Cells(r, c).Value <> "" Then
str = Cells(r, c).Value
Cells(r, c - 5).Value = getrecordset(str)
End If
Next r
Next c
End Sub