Searching using ADODB

[Sanu990]

I am trying to find data from access using the below code. The variable R is the date which it will search in the table. If date find in table, it will pickup data from some of the field else it will close the connection and loop will forward. Now the statement "On Error GoTo ST2" is working for the 1st time in loop but not from the 2nd time. Can anyone please help me find out what is wrong with the code? Thank you in advance.

Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Sheets("Load").Range("DB").Value & ";"
''''''Display calender
For i = DY To (DY + maxDate) - 1
SHT.Range("D0Y" & i).Value = (i - DY) + 1
''Calculate date
R = Format((i - DY) + 1, "00") & "-" & Format(Month(DT), "00") & "-" & Year(DT)
''Load leave data
Set Connection = New ADODB.Connection
Connection.Open ConnectionString:=Connect
Set Recordset = New ADODB.Recordset
Source = "SELECT * FROM Data WHERE [Leave_Date] = '" & R & "'"
With Recordset
On Error GoTo ST2
.Open Source:=Source, ActiveConnection:=Connection
Do While Recordset.EOF = False
If SHT.Range("D0YN" & i).Value = "" Then
SHT.Range("D0YN" & i).Value = Recordset.Fields(3).Value & "-" & Recordset.Fields(7).Value
Else
SHT.Range("D0YN" & i).Value = SHT.Range("D0YN" & i).Value & Chr(10) & Recordset.Fields(3).Value & "-" & Recordset.Fields(7).Value
End If
Recordset.MoveNext
Loop
End With
ST2:

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
Next i

 

[BiocideJ]

Basically, after you encounter the error you will need to clear it out before you hit another. Based on your looped design, the simplest way to do this will be to enter

On Error Goto -1

immediately following the ST2: line


Also, when posting code in the future, please use the

 tags

 

[Norie]

What error is this mean to handle?

On Error GoTo ST2

If the query returns no records it won't cause an error.

Also, why are you opening/closing the connection multiple times?

Open the connection outside the loop and close it once you are finished with it, after the loop.

You might also want to consider doing the whole thing in one query by using a start and end data in the criteria.

 

[BiocideJ]

What error is this mean to handle?
Also, why are you opening/closing the connection multiple times?

Open the connection outside the loop and close it once you are finished with it, after the loop.

You might also want to consider doing the whole thing in one query by using a start and end data in the criteria.

Yikes, I didn't even notice that. Good point.