How do I handle "Undefined function 'InStrRev' in expression." when connecting to MSAccess database and executing SQL in VBA?

Question

I'm attempting to execute some SQL by connecting to a MSAccess database. The SQL works perfectly as a query, but it encounters an error when trying to execute from within the Excel VBA. I utilize this same method do execute a lot of SQL through the database, and haven't encountered this error before.

Sub ReassignPostScript()
' Variable Declaration Start
    Dim MyConn, objRecordSet
    Dim sSql
    Dim myfd 'folder path for files (use network paths when available!)
    myfd = "Path\To\Database"
    Dim mydb 'For referencing a database
    mydb = "DatabaseName.accdb"
' Variable Declaration End

' Setting Environment and database connection start
    Set MyConn = CreateObject("ADODB.Connection")
    MyConn.Provider = "Microsoft.ACE.OLEDB.12.0;"
    MyConn.Open myfd & "\" & mydb

' Setting SQL statment to null
    sSql = ""
' Query to update tblName eCRM Activity String
    sSql = "UPDATE tblName SET tblName.InnoLog = Trim(Mid([InnoLog],InStr([InnoLog],' ')+1,(InStrRev([InnoLog],' ')+1)-(InStr([InnoLog],' ')+1))) " & _
    "WHERE (((tblName.InnoStatus) Like '%S:CRM_USERFACE:006%') and ((tblName.InnoLog) Like 'Transaction%') );"
    MyConn.Execute (sSql)

End Sub

The part that I'm trying to execute is the middle part of this string, between the two spaces

Any help/advice you all could provide would be greatly appreciated.

Thanks!

Not all Access functions are available when you query an Access DB from Excel using ADO. Those functions are implemented by the Access runtime, which is not used when querying using the ADO driver from an other application. https://stackoverflow.com/questions/848288/sql-through-classic-ado-undefined-function-round — Tim Williams, Nov 26 '18 at 17:35
`InStrRev` is a VBA function, defined in the `VBA.Strings` module. Access-SQL doesn't necessarily support all of those. — Mathieu Guindon, Nov 26 '18 at 17:38

swolfe2 · Accepted Answer · 2018-11-26T18:57:43.633

Thanks to Tim/Mathieu for the help.

I ended up just saving the query within the database, and then executing it through the VBA. A pretty easy workaround.

    Sub eCRMReassignPostScript()
'Option Explicit

' Variable Declaration Start
    Dim conn
    Dim MyConn, objRecordSet
    Dim sSql
    Dim myArray, strPoNumber, strSoldto, txtNote, str, myPath
    Dim myfd 'folder path for files (use network paths when available!)
    myfd = "\\knx-fs1.na.ad.whirlpool.com\Team\TELESERV\Continuous Improvement Db Files\Returns"
    Dim mydb 'For referencing a database
    mydb = "ReturnsToLoad.accdb"
    myPath = myfd & "\" & mydb
' Variable Declaration End

    ' Have to open database to execute macro, since it can't be called from the connection
    Dim strDatabasePath As String
    Dim appAccess As Access.Application
    strDatabasePath = myPath
    Set appAccess = New Access.Application
    With appAccess
        Application.DisplayAlerts = False
        .OpenCurrentDatabase strDatabasePath
        .DoCmd.OpenQuery "qryeCRMActivityStringUpdate"
        .Quit
    End With
    Set appAccess = Nothing

    ' Setting Environment and database connection start
    Set MyConn = CreateObject("ADODB.Connection")
    MyConn.Provider = "Microsoft.ACE.OLEDB.12.0;"
    MyConn.Open myfd & "\" & mydb

    ' Setting SQL statment to null
    sSql = ""
    ' Query to update tblName eCRM Activity String
    sSql = "UPDATE tblName INNER JOIN dbo_tblInnoweraReturns ON (tblName.ORDERACT = dbo_tblInnoweraReturns.ORDERACT) AND (tblName.Status = dbo_tblInnoweraReturns.Status) SET dbo_tblInnoweraReturns.Status = 'eCRM Reassigned - ' & [dbo_tblInnoweraReturns].[Channel], dbo_tblInnoweraReturns.CompletedOn = Now(), dbo_tblInnoweraReturns.CompletedBy = 'INNOWERA - REASSIGNED', dbo_tblInnoweraReturns.CompletedByTeam = 'INNOWERA - REASSIGNED', dbo_tblInnoweraReturns.CompletedByChannel = 'INNOWERA - REASSIGNED';"
    MyConn.Execute (sSql)

    ' Setting SQL statment to null
    sSql = ""
    ' Query to delete from local table where Activities are made
    sSql = "DELETE tblName.*, tblName.InnoStatus " & _
    "FROM tblName " & _
    "WHERE (((tblName.InnoStatus)='S:CRM_USERFACE:006'));"
    MyConn.Execute (sSql)

DoCmd.SetWarnings False

Set MyConn = Nothing
Set mydb = Nothing

End Sub

How do I handle "Undefined function 'InStrRev' in expression." when connecting to MSAccess database and executing SQL in VBA?

1 Answers1