Difference between Access Table and Linked Table from SQL

[dannybeaver]

Hi,



This is in relation to post: https://www.accessforums.net/access/...ext-46788.html

As mentioned in the above post, I have a code, situated in the criteria section of a field in a query.
This query is used as the data source for a sub form.
The subform is contained within a main form, which is where my "filtering textbox" is.

Say we have three records:
Chesterfield Steels
TM STEELS
alloy steels

I would like to be able to enter steels into the text box, and be displayed with all of the above records, due to the fact that they all contain: steels in some form.

My problem is this: The text box is only showing records that match exactly what is entered. So if I enter steels, I get the record: alloy steels; if I enter STEELS I get the record: TM STEELS and finally if I enter Steels, I get the record Chesterfield Steels.

No here is my question, the Query being used as the data source gets its information from a linked table, and I wondered if this could affect why the search does not work properly?

This is due to the fact that I recently created a test database from scratch, exactly the same as the current one, however it did not have a linked table; It used a table I created and then populated with the above three records and some other anomalies to prove the search works.

This time, the text box brought all three records back when steels was entered.

Does anyone have any information as to why it works in this way, and if there is a way to get it to work the way I want it to. Maybe there is some setting in SQL Server that I have to change?

By the way, the code that is in the criteria in the query is exactly the same in both of the databases, which is what led me to believe that the problem lies with SQL rather than with the database? The code is:

Like "*" & [Forms]![MainWindow].[NameTxt] & "*"

Any information is greatly received.

Thanks,
Danny

[ItsMe]

...the Query being used as the data source gets its information from a linked table, and I wondered if this could affect why the search does not work properly?...

I am going to guess your issue is with the design of your form. You mentioned that you are using a subform. It sounds as though you are using this subform as the means to filter the main form's recordset. This is not a good approach. Subforms are child to the main form. Main forms dictate the recordset of the subform object.

You mentioned that you use a textbox to support the Dynamic Parameterized query. The typical approach is to use a combo box. You would not use a bound control as criteria for an SQL statement and or a Query Object.

I would start with a basic SELECT query. I would test the SELECT query in design view by typing a literal value in the criteria field. When I can view my SELECT query in Datasheet view and it displays the expected results, I would try to replicate my achievement by creating a combobox to support a parameterized query. The combobox would pass values similar to the literal value I used when testing the SELECT query. Use a, basic, single view form when testing your new combobox and Dynamic Parameterized Query Object.

[dannybeaver]

we can kind of mark this issue as resolved, due to the fact that I now know what the problem is regarding.

The Collation of the linked table from SQL states that the table is Case Sensitive, only allowing matches that exactly match what the problem is.

My main form does not have a record set. My main form is simply used to hold the subform. This subform displays records from a query, which is how I believe it should be set up.

Thanks for the help.

[ItsMe]

...This subform displays records from a query, which is how I believe it should be set up.
...

I do this sort of thing often.

Here is a small amount of info regarding Binary. The main reason I am linking it here is because it is a recent thread and it is fresh in my memory. Just food for thought.
https://www.accessforums.net/program...tml#post245314