Solved Missing records using SQL Recordset

[Barkleflem]

Hi,

I'm a beginner at VBA and SQL and I've hit an issue trying to build a function which searches and selects records in a closed workbook and pastes the results in the active workbook. The problem I'm having is that only eight records are being returned, despite more matching the criteria.

The search runs across 12 sheets, each one covering a single month, looking for matching names. If the results exceed 8 records, nothing is pasted into the active workbook. The fields aren't particularly long and I'm not interested in very many columns. Below is the code I've written so far, followed by an image of the returned records.

Any suggestions, clarification or assistance would be well received.

Private Sub cmbSearch_Click()


    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SSearch As String
    
    SSearch = (txtSearch.Text)
      
    Sheets("DailySearch").Range("A1:Z100").ClearContents
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=\\DUCH-3900\Environment\ENV-SYS2\RSAFETY\SCHOOL CROSSING PATROL GROUP\SCP OPS Logs\2023 LOGS\TestData\Daily Log 2023.xlsx;" & _
        "Extended Properties='Excel 12.0 Xml;HDR=No';"

    cn.Open
    
    Set rs = New ADODB.Recordset
      
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [Jan$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Feb$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Mar$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [April$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [May$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [June$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [July$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Aug$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Sept$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Oct$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Nov$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

        rs.Open
        
        Sheet7.Range("A1").CopyFromRecordset rs
    
        Sheet7.Range("A1").CurrentRegion.EntireColumn.AutoFit

    rs.Close
    cn.Close
      
    DLogDisplay.ColumnCount = 18
    DLogDisplay.RowSource = "DailySearch!B1: AD100"

End Sub

 

[theDBguy]

Just curious, are all those 8 records in the result happen to be from the [Dec$] table?

PS. Also, how many records are in each table?

 

[Barkleflem]

Just curious, are all those 8 records in the result happen to be from the [Dec$] table?

PS. Also, how many records are in each table?

The number of records vary and the example I included are sourced from Jan$ through to May$, although some are missing.

 

[theDBguy]

The number of records vary and the example I included are sourced from Jan$ through to May$, although some are missing.

Thanks! And there are no overlaps of records?

I'm not familiar with the Source property, but are they supposed to stack up on each other when you assign the values in succession like that. My interpretation is it may be overwriting the previous assignment.

 

[Barkleflem]

Thanks! And there are no overlaps of records?

I'm not familiar with the Source property, but are they supposed to stack up on each other when you assign the values in succession like that. My interpretation is it may be overwriting the previous assignment.

I really don't know enough about this to structure things correctly - I'm working through and refining things as I go. It may be an overwriting issue but I wasn't sure of how to check through multiple sheets. I'll see if I can work on that as it does make sense.

 

[theDBguy]

I really don't know enough about this to structure things correctly - I'm working through and refining things as I go. It may be an overwriting issue but I wasn't sure of how to check through multiple sheets. I'll see if I can work on that as it does make sense.

If you could post a sample worksheet with all the other tables in separate tab/sheet, maybe someone here could write an example for you.

 

[Barkleflem]

If you could post a sample worksheet with all the other tables in separate tab/sheet, maybe someone here could write an example for you.

Unfortunately, I'm working with a copy of live data so I can't really share it. It's also drawing data from a predesigned spreadsheet so I'm stuck with the format they're using. I'll see if I can run up some dummy data to make things a little clearer.

 

[Barkleflem]

Thanks! And there are no overlaps of records?

I'm not familiar with the Source property, but are they supposed to stack up on each other when you assign the values in succession like that. My interpretation is it may be overwriting the previous assignment.

I've checked the run and it's overwriting the data. I must've been looking too closely to even think about that! Thank you for your help - I'll look at options for searching the whole workbook.

 

[theDBguy]

I've checked the run and it's overwriting the data. I must've been looking too closely to even think about that! Thank you for your help - I'll look at options for searching the whole workbook.

Good luck. Let us know how it goes.

 

[Isaac]

Do you realize that the final Source of rs is only the last line?

rs.Source = "SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

You change it like 10 times. That doesn't concatenate, nor Union, nor etc.

 

[Barkleflem]

Do you realize that the final Source of rs is only the last line?

rs.Source = "SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

You change it like 10 times. That doesn't concatenate, nor Union, nor etc.

I'm new to this so I didn't realise I couldn't stack the data. I'm trying to fix that now.

 

[adhoustonj]

You may be able to build a union query like below to get all records from each sheet.

rs.Source = “SELECT * FROM [Jan$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Feb$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Mar$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [April$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [May$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [June$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [July$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Aug$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Sept$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Oct$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Nov$] WHERE [F3] = '" & SSearch & "' UNION ALL " _
        & “SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

 

[Barkleflem]

You may be able to build a union query like below to get all records from each sheet.

rs.Source = “SELECT * FROM [Jan$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Feb$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Mar$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [April$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [May$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [June$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [July$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Aug$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Sept$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Oct$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Nov$] WHERE [F3] = '" & SSearch & "' UNION " _
        & “SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

Thanks for the suggestion. I've managed to resolve the issue now - it's not pretty but it works! I may look at using this method when I come to tidy everything up.

 

[Gasman]

I doubt it is ever going to pretty the way it is now.