Solved Use SQL Server Full text search (with 'contains') from Access VBA (1 Viewer)

Marnix

New member
Local time
Today, 19:48
Joined
Sep 15, 2020
Messages
9
Hi, I recently migrated a split access db to Azure SQL Server. Really happy with the performance of SQL Views in SQL Server.
I need to build a search form for searching text of supporttickets.
In TSQL in SSMS i can perform queries like this one:

SQL:
select * from Supporttickets where contains(TicketDescription, 'lookingforthisword')

The question is:
  1. How can I access the Full text functionality from Access VBA side?
  2. Is this the way to go or is there a better option?
Thanks in advance !
 

Minty

AWF VIP
Local time
Today, 18:48
Joined
Jul 26, 2013
Messages
10,355
Run it as a Pass-through query to a stored procedure that returns a recordset.
Or just as a straight passthrough query.
 

Isaac

Lifelong Learner
Local time
Today, 11:48
Joined
Mar 14, 2017
Messages
8,738
What Minty said.
 

Marnix

New member
Local time
Today, 19:48
Joined
Sep 15, 2020
Messages
9
Thanks for responding. I tried, but still the SQL seems to be evaluated by access.
Code:
Dim qdf As DAO.QueryDef, rs As DAO.Recordset, strSQL As String, val As String
    
    strSQL = "select * from Notities where contains(notitie, 'test')"
    Set qdf = CurrentDb.CreateQueryDef("MyPTQ")
    qdf.SQL = strSQL
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    If rs.BOF And rs.EOF Then
        MsgBox "there is no such word"
    Else
        val = rs.Fields(0).Value
    End If
    rs.Close
End Sub

This returns when opening the recordset error 3085: "Undefined function contains" although this is valid TSQL.
 

Minty

AWF VIP
Local time
Today, 18:48
Joined
Jul 26, 2013
Messages
10,355
I use a generic PT query a lot - you pass it a T_SQL command string and it either performs an action (An SP that does stuff, but doesn't return records) or sets up the query to then be used;

SQL:
Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean)

    Dim db As DAO.Database
    Dim qDef As QueryDef
  
    Set db = CurrentDb()
  
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("insert_a_Current_Linked_db_Table_here").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
  
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close
    End If
  
    Set qDef = Nothing
    Set db = Nothing

End Sub

So in your calling code you would do the following for instance
Code:
strSQL = "EXECUTE sp_set_database_firewall_rule N'" & sName & "', '" & strInputForm & "' , '" & strInputForm & "' "
sSendToPT_Generic strSQL, False
This would execute the code.

This would set the query up to return a result
Code:
    strSQL = "EXEC [dbo].[sp_REPMonthly_Management] @dStart = " & ServerDate(dStart) & " , @dEnd = " & ServerDate(dEnd)
  
    sSendToPT_Generic strSQL, True
You then simply open qPT_Generic into a recordset, or use it as a snapshot recordsource for a form.

In most DB's I have 3 or 4 qPT_'s that I use for reports, forms or other things.

Edit - Your code is failing because you are trying to create a new query every time. ( Set qdf = CurrentDb.CreateQueryDef("MyPTQ")
 

Marnix

New member
Local time
Today, 19:48
Joined
Sep 15, 2020
Messages
9
THanks a lot Minty and Isaac for your replies. Really appreciate this! Will work with the answers tomorrow and let you know the results to close this topic.
 

Marnix

New member
Local time
Today, 19:48
Joined
Sep 15, 2020
Messages
9
Hi Minty and Isaac. Thanks again for helping. I struggled a bit getting it to work, mainly because I want to use it DSN-less and in code. Now it works.
Needed to add the connection string as qdf.connect = "ODBC;Driver = etc. etc. .
It also took me some time to find out that the TSQL is sensitive to wildcard on wrong position and needs "" if wildcard is used. But now I can execute this query as passthrough without access SQL parsing intervening.
The result is worth the hassle. It's lightening fast even with queries like
Code:
select * from notes where contains (notetext,'"(searchtxt1 And "searchtxt2*") OR (searchtxt3 AND searchtxt4)"'
I will mark this one as solved.
 

Isaac

Lifelong Learner
Local time
Today, 11:48
Joined
Mar 14, 2017
Messages
8,738
Hi Minty and Isaac. Thanks again for helping. I struggled a bit getting it to work, mainly because I want to use it DSN-less and in code. Now it works.
Needed to add the connection string as qdf.connect = "ODBC;Driver = etc. etc. .
It also took me some time to find out that the TSQL is sensitive to wildcard on wrong position and needs "" if wildcard is used. But now I can execute this query as passthrough without access SQL parsing intervening.
The result is worth the hassle. It's lightening fast even with queries like
Code:
select * from notes where contains (notetext,'"(searchtxt1 And "searchtxt2*") OR (searchtxt3 AND searchtxt4)"'
I will mark this one as solved.
I'm very glad to see you got it working and can relate to the feeling of satisfaction in letting T-SQL do the work with the resulting speed and vastly increased platform potential. (y)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:48
Joined
Jan 20, 2009
Messages
12,849
the feeling of satisfaction in letting T-SQL do the work with the resulting speed and vastly increased platform potential. (y)
Of course it isn't just that it is T-SQL. Contains use the Full Text Index capabilities that crawl the text and build an index of the words. It is very powerful, including being able to recognise the words in different tenses and semantics.

With FileStream and FileTables it can be used to index entire documents in many formats.
 

Isaac

Lifelong Learner
Local time
Today, 11:48
Joined
Mar 14, 2017
Messages
8,738
Of course it isn't just that it is T-SQL.
Of course not. I just meant the various capacities that SQL Server has being opened up in a general sense, and, as you point out, in this specific sense.
 

Users who are viewing this thread

Top Bottom