Link to home
Start Free TrialLog in
Avatar of Connie Jerdet-Skehan
Connie Jerdet-SkehanFlag for United States of America

asked on

query linked sql table field from access

I am using access 365 and Sql Server 2014

I have two buttons, one to show all records in a linked sql table,the other, to perform a search for keywords in the same linked sql table.
The table is linked and connected using adodb.connection when the access database is opened. Please show me what changes I need to make for the desired results.

The code I am using is not producing the results I am looking for.
below is my code for each

To show all records in table

dim sql as string.

sql = "SELECT company.companyId, company.companyName, company.companyAddress, company.companyCity, company.companyState, company.companyZip, company.companyPhone, company.InCareOfName, company.Exclude, company.Keep, company.Reason, company.AccessNumber, company.Change " _
    & "FROM company " _
    & "ORDER BY company.companyName "
   
   
    Me.txtPltfkeywords = ""
    Me.companyList.Form.RecordSource = sql
    Me.companyList.Form.Requery

To search for keywords within the table

Dim sql As String

    sql = "SELECT company.companyID, company.companyName, company.InCareOfName, company.Exclude, company.Reason, company.companyPhone, company.companyAddress, company.companyCity, company.companyState, company.companyZip, company.Keep, company.AccessNumber, company.Change " _
& "FROM company " _
& "WHERE companyName Like ""*" & txtPltfkeywords & "*"" Or companyAddress Like ""*" & txtPltfkeywords & "*"" Or companyCity Like ""*" & txtPltfkeywords & "*"" Or Reason Like ""*" & txtPltfkeywords & "*"" Or companyState Like ""*" & txtPltfkeywords & "*"" Or companyZip Like ""*" & txtPltfkeywords & "*"" Or companyPhone Like ""*" & txtPltfkeywords & "*"" Or InCareOfName Like ""*" & txtPltfkeywords & "*""" _
& "ORDER BY company.companyName "
   
    Me.companyList.Form.RecordSource = sql
    Me.companyList.Form.Requery
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If I seeing it right, use % instead of *. That's the correct equivalent of * in SQL Server.
Avatar of Connie Jerdet-Skehan

ASKER

Your suggestion worked after a typo error I made. Thank you.
If you are querying a linked table then you will use *...if you are using a passthrough query you should use %
Take your query to SSMS and change the wildcard from * to %...check the results and report back...