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

[Marnix]

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:

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]

Run it as a Pass-through query to a stored procedure that returns a recordset.
Or just as a straight passthrough query.

 

[Isaac]

What Minty said.

 

[Marnix]

Thanks for responding. I tried, but still the SQL seems to be evaluated by access.

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.

 

[Isaac]

You have to actually create a query in Access and go to its design properties and set to be pass-through type, build the odbc connection string, etc. Then put your sql into it

Create a pass-through query - Access (microsoft.com)

 

[Minty]

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;

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

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

    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]

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]

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

select * from notes where contains (notetext,'"(searchtxt1 And "searchtxt2*") OR (searchtxt3 AND searchtxt4)"'

I will mark this one as solved.

 

[Isaac]

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

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.

 

[Galaxiom]

the feeling of satisfaction in letting T-SQL do the work with the resulting speed and vastly increased platform potential.

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]

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.