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,362
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,082,246
Messages
5,363,984
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top