Access Linked to SQL Server partial failure - is there a known limit to query (1 Viewer)

[Rx_]

Split Access 2010 Database connected to SQL Server 2008r2 via DSN-Less Linked tables using SQL Server Native Client 11.0.
A procedure destroys all linked tables and re-creates them so that an Access Front-end can connect between a SQL Production and SQL Test DB.

This is really a strange problem. I would greatly appreciate any ideas slightly outside Crop Circles (but only slightly).

Note: the Front-End Access go through controlled version releases.
This problem is exactly the same on the current front-end release or on one three months old. The problem only appears on SQL Server DB since last Friday (as per backup and restore efforts prove).

Problem: The User reported that the main Entity Search would not work for "Fertile".
The Production version would not work, the Test version worked.
The SQL Test DB was 1 week old.
A 3 week old Front-end Access version had exactly the same result.

The Search can take from 1 letter or number combination and search a view, every column. So a "C" will find all site names, state appreviations, status, and other columns with a "C" and report all records where a "C" is in any column. Likewise entering "Fox" returns the equal of *Fox* for all records, all columns.

Here is the strange part:
the letter A-D and G, K, M, O, P, Q, S, U, V, W, X, Y, Z will work along with all numbers.
the letters E, F, H, I, J, L, R, T will NOT work.

See Attachment - blured out actual data
Turns out "Fertile", see list above, returns empty.
On the SQL Test DB, I reloaded the nightly backups. The Thursday Night backup works fine. The Friday night (and beyond) does not work.
In the US we have a TV show called Wheel Of Fortune. the letters E, F, H, I, J, L, R, T seemed to have something in common with that show for most common letters.
1. On SQL Server DB, I ran various DBCC but everyting is fine.
2. On SQL Server, I manually re-set all indexes for every table used in the SQL Server View for the list box. The Query search and List box use this SQL Server View.
3. There was heavey new data entry for items in the view last Wed, Thurs, Fri, new records.
The data is OK. In the name field Fertile it is followed with numbers.
e.g. Fertile 101-10H Entering just 101-10H will return this record and any other instances of 101-10H.
The search is on about 20,000 records with about 12 columns.
It is just interesting that this happened with all these letters at once.
One almost suspects a B-Tree index.

 

[Rx_]

Discovered some code maintenance about 8 months ago that included PKA (Previous Known As) column.
It appears the PKA was in heavy update mode last week.
This code uses a record set to append to the query.
My guess is that it reached its limit Friday.

Private Function WellNameSearchPKA(pctl As Control) As Variant
    On Error GoTo ErrorHandler
    Dim strLogMessage As String
    Dim db As DAO.Database
    Dim rsWellNamePKA As DAO.Recordset
    Dim strSQL As String
    Dim strWellIDs As String
    
    Set db = DBEngine(0)(0)
    
    strSQL = "SELECT ID_Wells FROM Wells_LastKnownAs WHERE WLastKnown Like ""*" & Replace(Replace(pctl.Value, "'", "''"), "#", "[#]") & "*"";"
    
    Set rsWellNamePKA = db.OpenRecordset(strSQL, dbOpenForwardOnly)
        If rsWellNamePKA.BOF = True And rsWellNamePKA.EOF = True Then GoTo ProcedureExit
    
    Do Until rsWellNamePKA.EOF = True
        strWellIDs = strWellIDs & ", " & rsWellNamePKA!ID_Wells
        
        rsWellNamePKA.MoveNext
    Loop
    
    WellNameSearchPKA = " Or ID_Wells In (" & Mid(strWellIDs, 3) & ")"
     ' Rx_  I suspect the IN statement reached some magical limit.
ProcedureExit:
    On Error Resume Next
    rsWellNamePKA.Close
    Set rsWellNamePKA = Nothing
    Set db = Nothing
    Exit Function

 

[Rx_]

Nobody responded, so I am replacing my previous wrong "solution".
After more research, the problem was a little more bizzare.
There were some manual deletes going on, a question came up for the client they had not answered regarding the related tables.

Bad data broke RI rules, so the SQL inserted into the Listbox's Rowsource was defective, so the Listbox just silently failed.

So, the RI for a site named "Fertile 333-33" was in violation.
That explained the Characters that perfectlly matched Fertile.
I had stated the numbers would search earlier, turns out the 3 would not search either.

Basically, the search code returned some bad SQL. The VBA created the SQL that when put into a query view was... just bad.
Using VBA, the SQL is used to update the Listbox's Rowsource for each search.
Since the SQL was bad (due to RI violation) the List box just silently fails.

This explained why the SQL Database before the table edit Friday failed, while the SQL Database from before that time worked.

Mental note to self - don't do manual back end cleanup part way waiting for an answer.