Searching using ADODB

Sanu990

New Member
Joined
Jul 29, 2018
Messages
1
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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

Code:
On Error Goto -1
immediately following the ST2: line


Also, when posting code in the future, please use the
Code:
 tags
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
What error is this mean to handle?
Code:
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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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.
 

Forum statistics

Threads
1,085,041
Messages
5,381,345
Members
401,734
Latest member
cvickers81

Some videos you may like

This Week's Hot Topics

Top